Great tips. In response to your request, the Promoted Headers tip was new to me. I had already discovered the others before now. ... I agree with you that these tips are very useful. We don’t have big data requirements, so I don’t expect that they improve performance much for us, but they simplify the code (steps) & make maintenance much easier.
Go beyond comfort zone, beyond habits work only with Power Query UI and try to play with M-code in formula bar. Thank you Chandeep! Very cool kick in right direction)
Awesome, That what I have been doing in exploring M Code write at the beguining of my learning journey. I tweak or fine tune the code by 1. Nesting functions 2. Exploiting fully the functions optional arguments like data types, column names
Pretty good ...Chandeep. Another way I reduce the steps is the nesting of queries. However, a word of caution is to avoid too many nested queries as they become difficult to debug later.
Thanks a lot. I learned a whole new way of looking at my M code. Question. Apart from the aesthetics, is there any harm to having more steps? Is there a significant penalty in speed or file size?
I actually have the same question (sort of). Ken Puls explicitly mentions on his website it does not make sense to combine query steps into one. Since the code will work inside out. But that is about nesting functions as I understand it. In that case more can be better and can have a positive impact on maintenance as well. Using extra arguments inside the function that replaces an extra step will have an impact. Hard to tell how much that impact is. I know in PowerBI there is a way to test it. Leaving Columns as any type does have an impact on memory and data storage. Any consumes the most. So I'm guessing there is some debate whether all these tips are to be followed blindly each and every time. Context is everything in PowerBI ;-). @chandeep @goodly, I'm kind of curious to know your extra insights on the topic. I'm almost certain you do have them.
- In PBI you can use Query diagnostics to test the performance of each step. - Also you're right about most solutions are contextual! - I often use these tricks to not pollute the number of steps in my query!
@@GoodlyChandeep , thanks for answering. I know those diagnostics exists. Still in step-in mode when it comes down to PowerBI. Somehow I'm sticking to Excel for the moment. All 'bout comfort zone , right? I don't mind extra steps in my queries as long as they are not somehow redundant. Like many of your examples.
This is very good information, thank you. May I suggest adding chapter markers for each of the tips. I came back to look again at the tip regarding adding headers in one step and I had to search through all the video to find it. Again, I appreciate your content and the work you put into it. I have learned a lot and I am grateful.
As usual, very good video Chandeep! I know you can not please all people in the world, but the background music is no pleasure for me, since I have to concentrate on the English...
Thanks Bart, just experimenting stuff (in terms of music). If a lot of people feel alike, I'll certainly stop :) Hope that's not too much of a hassle. Thanks again!
Nice tricks, I am using them all, but still do not understand when and how to use table buffer so I merge few times large data sources and speed up the query refresh. Sometimes it takes 30-40 minutes and I am merging 2-3 or more times the same tables, where I think Table.buffer may help... I will be happy if you make a video ot this topic. There are no many and no good explanations on how to use it and when... Thank you!
Create a dedicated playlist on power query functions (M language)... Not much content is available online for that.. And yes... Great content... Keep up the good work
A few good extra hints here. I like to work on the principal of reduced steps. Was there an easy way to replace multiple values in one batched step? I looked at a few suggestions, but they were really long winded & I tried to guess a few variations but had no joy.
While combining files, Merge columns getting stuck if some time one column is not there. Means column name is dynamic but value type in columns is same which is why merging is required.
Great Video my friend: Question: Is there a way to ADD a column from the Source Data to a Query After the Query has been created an loaded to a Table? Thank-you for the helpful information!
My colleague said that once he got a .pbix file to work with, and when he tried to open it didn't open and there was an error that there isn't enough ROM to open it (he had 16 gb). When he managed to open it in cloud virtual machine he said that there was a tons of steps in power query which caused problem with calculating it.
Everyone talks about expand column & tables but I was wondering how can we pack our 1000s rows of data into 100s rows of data so that we can later again expand those.
Hi. It is obvious that reducing the steps simplifies the whole code (sometimes not ;-). But PQ in my case is terribly slow. Clicking on any step requires massive amounts of data to be refreshed. Sometimes it is impossible to work with. That's why I wanted to ask: do You have a video with 5 Tricks to Speed up Work Power Query? Great job as always!!!
I'll have to create a video on that. If you can explain your problem clearly and send me a few examples that slow down your query I can create something interesting. goodly.wordpress@gmail.com Thanks Chandeep
Hey, your tricks are awesome and really very relevant to the topics thanks a lot. I would request you to please make some videos on mekko custom visual.
Promoted Headers code...new trick for old dog. Why reduce steps (some of your viewers ask), because when you go back you will reduce the number of WTF? moments in looking at your query steps. I'm still a new user to PQ--and I evangelize its virtues to many. (I realize that I am one of MANY that have managed to overlook this essential tool. I also find that if I use the F2 button and rename the steps, particularly for adding columns for calculations; it allows for a cleaner (re)view of the query.
So you turned off auto detect data type then showed how to manually apply auto detect data type? Seems like you like fixed a problem that you created 😂
Great video Chandeep, very useful!
Thanks for the shout-out! Means a lot!
Thank you.
Another option for the last step: You can combine source and navigation. Copy the {}-part from nav to the source.
Fantastic video, Goodly. I use Power Query every day in my day job so this will come in very useful.
Thanks, very useful!
I never realised that I should use more parameters in functions, this changes my thinking. Time to prune my queries :)
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Great tips. In response to your request, the Promoted Headers tip was new to me. I had already discovered the others before now. ... I agree with you that these tips are very useful. We don’t have big data requirements, so I don’t expect that they improve performance much for us, but they simplify the code (steps) & make maintenance much easier.
Last one is the best...
Thanks for consolidating the 💡. We will definitely use.
Your are excellent 👏👌 in marking the things simple.
Go beyond comfort zone, beyond habits work only with Power Query UI and try to play with M-code in formula bar. Thank you Chandeep! Very cool kick in right direction)
explanation is too good. looking for more video's like this
This is insanely good. Thank you Chandeep!
Thanks Chandeep , you make every thing easy for us, Abeer from Egypt
Great tip re promoting headers. Thanks
This is a good example of how knowing just a little bit of manual coding can give big benefits.
Indeed!
Thanks a million for this very useful Video Chandeep!
Awesome, That what I have been doing in exploring M Code write at the beguining of my learning journey. I tweak or fine tune the code by
1. Nesting functions
2. Exploiting fully the functions optional arguments like data types, column names
Pretty good ...Chandeep. Another way I reduce the steps is the nesting of queries. However, a word of caution is to avoid too many nested queries as they become difficult to debug later.
Thank you Goodly. I learnt all these tricks though trial and error but it's good to know I've been doing the right thing.
Awesome!
I like timestamps
0:45 Reduce Changed type steps
5:10 Reduce reorder columns steps
7:22 Apply batching
9:49 Reduce renaming columns
13:30 Reduce promoted headers
cheers
Great tips, Chandeep. The promote headers and add date ones are especially helpful! Thanks
Glad it was helpful!
Nice Explanation Chandeep...
Nice! Very handy and simple tricks :)
Glad you liked it!
Promoted Header was unknown to me. Thanks for sharing this.
pleasure!
Thanks Chandeep for nice tips ..
Always welcome!
Thanks a lot. I learned a whole new way of looking at my M code. Question. Apart from the aesthetics, is there any harm to having more steps? Is there a significant penalty in speed or file size?
I actually have the same question (sort of). Ken Puls explicitly mentions on his website it does not make sense to combine query steps into one. Since the code will work inside out. But that is about nesting functions as I understand it. In that case more can be better and can have a positive impact on maintenance as well.
Using extra arguments inside the function that replaces an extra step will have an impact. Hard to tell how much that impact is. I know in PowerBI there is a way to test it.
Leaving Columns as any type does have an impact on memory and data storage. Any consumes the most. So I'm guessing there is some debate whether all these tips are to be followed blindly each and every time. Context is everything in PowerBI ;-).
@chandeep @goodly, I'm kind of curious to know your extra insights on the topic. I'm almost certain you do have them.
- In PBI you can use Query diagnostics to test the performance of each step.
- Also you're right about most solutions are contextual!
- I often use these tricks to not pollute the number of steps in my query!
@@GoodlyChandeep , thanks for answering. I know those diagnostics exists. Still in step-in mode when it comes down to PowerBI. Somehow I'm sticking to Excel for the moment. All 'bout comfort zone , right?
I don't mind extra steps in my queries as long as they are not somehow redundant. Like many of your examples.
Very good tips. Thanks
This is very good information, thank you.
May I suggest adding chapter markers for each of the tips. I came back to look again at the tip regarding adding headers in one step and I had to search through all the video to find it.
Again, I appreciate your content and the work you put into it. I have learned a lot and I am grateful.
Sure would do!
Thanks, very useful!
Great video!
Do you have early morning live classes ??
Great tricks! Thanks :-)
As usual, very good video Chandeep! I know you can not please all people in the world, but the background music is no pleasure for me, since I have to concentrate on the English...
Thanks Bart, just experimenting stuff (in terms of music). If a lot of people feel alike, I'll certainly stop :)
Hope that's not too much of a hassle. Thanks again!
Good one 👍 Thank you!
Glad you liked it!
Nice tricks, I am using them all, but still do not understand when and how to use table buffer so I merge few times large data sources and speed up the query refresh. Sometimes it takes 30-40 minutes and I am merging 2-3 or more times the same tables, where I think Table.buffer may help...
I will be happy if you make a video ot this topic. There are no many and no good explanations on how to use it and when...
Thank you!
very informative. Can u tell how to unlink the all quires in one step ,once work done ?
promote headers is great step to reduce in Power query
My best PQ trick is to watch videos from Goodly 😊😊
Glad to hear it!
This video is nothing but AWESOME, ;)..!!!
Glad you liked it!
Create a dedicated playlist on power query functions (M language)... Not much content is available online for that..
And yes... Great content... Keep up the good work
A few good extra hints here. I like to work on the principal of reduced steps.
Was there an easy way to replace multiple values in one batched step?
I looked at a few suggestions, but they were really long winded & I tried to guess a few variations but had no joy.
You can do that using a list accumulate function
Thank you
Good tricks
Sir, More steps make File size larger?
While combining files, Merge columns getting stuck if some time one column is not there. Means column name is dynamic but value type in columns is same which is why merging is required.
Is there a performance issue if we reduce steps?
Mostly it's a performance boost not an issue.
Great Video my friend: Question: Is there a way to ADD a column from the Source Data to a Query After the Query has been created an loaded to a Table? Thank-you for the helpful information!
??? You can just modify the query and add any column you want?
Very interesting video for someone just getting started. Question: What is the benefit of reducing steps?
See this - ua-cam.com/video/2bzJFrJPg_c/v-deo.html
Thank you
My colleague said that once he got a .pbix file to work with, and when he tried to open it didn't open and there was an error that there isn't enough ROM to open it (he had 16 gb). When he managed to open it in cloud virtual machine he said that there was a tons of steps in power query which caused problem with calculating it.
Everyone talks about expand column & tables but I was wondering how can we pack our 1000s rows of data into 100s rows of data so that we can later again expand those.
Hi. It is obvious that reducing the steps simplifies the whole code (sometimes not ;-). But PQ in my case is terribly slow. Clicking on any step requires massive amounts of data to be refreshed. Sometimes it is impossible to work with. That's why I wanted to ask: do You have a video with 5 Tricks to Speed up Work Power Query?
Great job as always!!!
I'll have to create a video on that.
If you can explain your problem clearly and send me a few examples that slow down your query I can create something interesting. goodly.wordpress@gmail.com
Thanks
Chandeep
Need to get in touch with you
Hey, your tricks are awesome and really very relevant to the topics thanks a lot.
I would request you to please make some videos on mekko custom visual.
renaming the columns in M code rather than using an applied step is genius, so simple but wasn't obvious to me
finished watching
1:45
Promoted Headers code...new trick for old dog. Why reduce steps (some of your viewers ask), because when you go back you will reduce the number of WTF? moments in looking at your query steps. I'm still a new user to PQ--and I evangelize its virtues to many. (I realize that I am one of MANY that have managed to overlook this essential tool. I also find that if I use the F2 button and rename the steps, particularly for adding columns for calculations; it allows for a cleaner (re)view of the query.
पहप्पे, आपके विडिओ बहुत कामके हैं , पर यार पीछे बाजा क्यों बजते हो , बड़ा distracting होता है
So you turned off auto detect data type then showed how to manually apply auto detect data type? Seems like you like fixed a problem that you created 😂