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
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)
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.
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.
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!
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!
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.
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.
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.
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
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
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.
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.
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.
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.
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
This is a good example of how knowing just a little bit of manual coding can give big benefits.
Indeed!
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)
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.
Thanks, very useful!
I never realised that I should use more parameters in functions, this changes my thinking. Time to prune my queries :)
Fantastic video, Goodly. I use Power Query every day in my day job so this will come in very useful.
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
Last one is the best...
Thanks for consolidating the 💡. We will definitely use.
Your are excellent 👏👌 in marking the things simple.
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 Chandeep , you make every thing easy for us, Abeer from Egypt
My best PQ trick is to watch videos from Goodly 😊😊
Glad to hear it!
This is insanely good. Thank you Chandeep!
promote headers is great step to reduce in Power query
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!
Great tip re promoting headers. Thanks
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!
explanation is too good. looking for more video's like this
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.
Thanks a million for this very useful Video Chandeep!
Promoted Header was unknown to me. Thanks for sharing this.
pleasure!
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!
renaming the columns in M code rather than using an applied step is genius, so simple but wasn't obvious to me
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 Explanation Chandeep...
Great tips, Chandeep. The promote headers and add date ones are especially helpful! Thanks
Glad it was helpful!
Thank you
Good tricks
Sir, More steps make File size larger?
Thanks Chandeep for nice tips ..
Always welcome!
very informative. Can u tell how to unlink the all quires in one step ,once work done ?
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.
Very good tips. Thanks
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
Do you have early morning live classes ??
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
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?
Thanks, very useful!
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.
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
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
This video is nothing but AWESOME, ;)..!!!
Glad you liked it!
Great video!
Thank you
finished watching
Nice! Very handy and simple tricks :)
Glad you liked it!
Good one 👍 Thank you!
Glad you liked it!
Great tricks! Thanks :-)
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.
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.
Is there a performance issue if we reduce steps?
Mostly it's a performance boost not an issue.
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.
Need to get in touch with you
1:45
पहप्पे, आपके विडिओ बहुत कामके हैं , पर यार पीछे बाजा क्यों बजते हो , बड़ा 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 😂