Hi Riick Great Post. . you gave me great ideas on how to document my current queries at work. One thing that i have noticed is that when you add comments in powerquery for excel the info icons don't show in your steps like it does in powerbi. Also, i will buy the Gil Raviv' s book . I have the Ken puls/miguel escobar book already. Quick question : i have a chart of accounts that are not sorting correctly ( They are sorting like text instead of a numbers) , Do you have a post where i can read and follow along? Thanks for posting and stay safe
Jazzista! Always great seeing you back on the channel. In regards to the Chart of Account. If your column is a text column, try adding a separate column for the sorting. In your data model (after loading Power Query), you can then in the data view select your text column, and sort this column by another one. If you here select the other column, you will solve your issue. I may launch a video on this later, good suggestion. Keep crushing it! Rick
HI Rick. Good morning and thanks for your reply, I will try to add a column like you suggested in my reports . It seems relatively easy though . I hate seen numbers sorted as text because it creates confusion when looking at accounts balances and when trying to preform analysis . I am always in the lookup of your videos: They are extremely informative and to the point. Please keep them coming my friend. Stay safe and thanks
one more question can I apply steps to other excel files like excel 1 file I have done some cleaning & transformation and I want to apply same steps to excel 2 file.
Yes you can do that. 1. You can copy your query in the query and connections pane, and paste it into the other Excel file. 2. You can go into the advanced editor, and copy the code from file 1, and paste this code in a query in file 2. Hope that helps!
Hey Filipe. Two books are great: Gil Raviv's - Collect, Combine, and Transform Data Using Power Query in Excel and Power BI Ken Pul's - M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query The first book is relatively new. The last one is older, but the gets the basics right. And make sure to read the series from Ben Gribaudo: bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let I also share plenty of articles on my blog gorilla.bi and you can find youtube videos on my channel on Power Query. Enjoy!
@@felipesignorellireis7839 I don't think I got it. If there's a specific problem you'd like addressed, please feel free to add it as suggestion for a future video :)
Hello I have one question is this necessary to learn M language to expert level for power query or only power query knowledge is enough. sometime it is difficult to find out solution for error in power query please make video on errors in power query.
Hi Lucky. Necessary is a broad word. If you want to become an Expert in Power Query I would argue it's good to know the M syntax. Because a lot of transformations are not possible with the user interface. For more complex situations custom M writing comes in handy. For error messages in Power Query, you can have a look at my video about using the if function in Power Query. At the end of the video I delve into error messages :th-cam.com/video/a8ph8Db2WbY/w-d-xo.html
Teacher, Do you know how I Can referer a parameter number inside a formula in custom column? For example: Let's say I creat a parameter in Power query that has a list of 3 number. 1, 5 or 10 and this parameter name is "Test" Then, a create a custom column with an IF formula: If [column] = "Red" then [Value] * 2 else [Value] * 2. Well,let's that I want to change de number multiplier to the number o the parameter list. So the formula could be If [column] = "Red" then [Value] * "Teste" else [Value] *2. So, do you know how is the syntax to do similar or if is possible do to this? Chears
Hi Felipe! You can reference variables by writing their name in the formula box. My recommendation would be to write a parameter name without spaces. Then you can reference the parameter by writing it's name without quotations. So: Teste * [Value], should work 😁 cheers!
Love the videos man, wish you had way moooore views. Such great content!!
thanks Jorge. Will be posting more the coming time :) !
I love your each video sir keep it up..
Hi Riick Great Post. . you gave me great ideas on how to document my current queries at work. One thing that i have noticed is that when you add comments in powerquery for excel the info icons don't show in your steps like it does in powerbi. Also, i will buy the Gil Raviv' s book . I have the Ken puls/miguel escobar book already. Quick question : i have a chart of accounts that are not sorting correctly ( They are sorting like text instead of a numbers) , Do you have a post where i can read and follow along? Thanks for posting and stay safe
Jazzista!
Always great seeing you back on the channel.
In regards to the Chart of Account. If your column is a text column, try adding a separate column for the sorting.
In your data model (after loading Power Query), you can then in the data view select your text column, and sort this column by another one.
If you here select the other column, you will solve your issue.
I may launch a video on this later, good suggestion.
Keep crushing it!
Rick
HI Rick. Good morning and thanks for your reply, I will try to add a column like you suggested in my reports . It seems relatively easy though . I hate seen numbers sorted as text because it creates confusion when looking at accounts balances and when trying to preform analysis . I am always in the lookup of your videos: They are extremely informative and to the point. Please keep them coming my friend. Stay safe and thanks
one more question can I apply steps to other excel files like excel 1 file I have done some cleaning & transformation and I want to apply same steps to excel 2 file.
Yes you can do that.
1. You can copy your query in the query and connections pane, and paste it into the other Excel file.
2. You can go into the advanced editor, and copy the code from file 1, and paste this code in a query in file 2.
Hope that helps!
Teacher.
With Book do you recommend for me to improve Power Query?
Hey Filipe. Two books are great:
Gil Raviv's - Collect, Combine, and Transform Data Using Power Query in Excel and Power BI
Ken Pul's - M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query
The first book is relatively new. The last one is older, but the gets the basics right. And make sure to read the series from Ben Gribaudo:
bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let
I also share plenty of articles on my blog gorilla.bi and you can find youtube videos on my channel on Power Query.
Enjoy!
@@BIGorilla Thanks teacher. I Just bought The M.
I'll check your blog for shure.
@@BIGorilla Hi Rick, I Sent to you by e-mail a a challenge. Hope you Can help. Regards
@@felipesignorellireis7839 I don't think I got it. If there's a specific problem you'd like addressed, please feel free to add it as suggestion for a future video :)
Hello I have one question is this necessary to learn M language to expert level for power query or only power query knowledge is enough.
sometime it is difficult to find out solution for error in power query please make video on errors in power query.
Hi Lucky.
Necessary is a broad word. If you want to become an Expert in Power Query I would argue it's good to know the M syntax. Because a lot of transformations are not possible with the user interface. For more complex situations custom M writing comes in handy.
For error messages in Power Query, you can have a look at my video about using the if function in Power Query. At the end of the video I delve into error messages :th-cam.com/video/a8ph8Db2WbY/w-d-xo.html
@@BIGorilla thank you so much for quick response
Teacher,
Do you know how I Can referer a parameter number inside a formula in custom column?
For example:
Let's say I creat a parameter in Power query that has a list of 3 number.
1, 5 or 10 and this parameter name is "Test"
Then, a create a custom column with an IF formula:
If [column] = "Red" then [Value] * 2 else [Value] * 2.
Well,let's that I want to change de number multiplier to the number o the parameter list. So the formula could be
If [column] = "Red" then [Value] * "Teste" else [Value] *2.
So, do you know how is the syntax to do similar or if is possible do to this?
Chears
Hi Felipe!
You can reference variables by writing their name in the formula box. My recommendation would be to write a parameter name without spaces. Then you can reference the parameter by writing it's name without quotations.
So: Teste * [Value], should work 😁 cheers!
@@BIGorilla Hi, It works. So simple. Thank you very much