5 Tricks to Reduce Steps in Power Query
HTML-код
- Опубликовано: 23 июл 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Blog - - - -
www.goodly.co.in/calculate-gr...
- Romania PUG RUclips Channel / @ssbicentral
- Romania PUG Meetup www.meetup.com/romaniapug/
---
► Artist Attribution
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall RUclips Channel Below
ruclips.net/channel/UCGQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses - Наука
Great video Chandeep, very useful!
Thanks for the shout-out! Means a lot!
explanation is too good. looking for more video's like this
Thanks, very useful!
I never realised that I should use more parameters in functions, this changes my thinking. Time to prune my queries :)
Thank you.
Another option for the last step: You can combine source and navigation. Copy the {}-part from nav to the source.
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.
This is insanely good. Thank you Chandeep!
Last one is the best...
Thanks for consolidating the 💡. We will definitely use.
Your are excellent 👏👌 in marking the things simple.
Great tip re promoting headers. Thanks
Fantastic video, Goodly. I use Power Query every day in my day job so this will come in very useful.
Thanks a million for this very useful Video Chandeep!
Very good tips. Thanks
Thanks, very useful!
Great tricks! Thanks :-)
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.
Great tips, Chandeep. The promote headers and add date ones are especially helpful! Thanks
Glad it was helpful!
Thanks Chandeep , you make every thing easy for us, Abeer from Egypt
Nice Explanation Chandeep...
Great video!
Nice! Very handy and simple tricks :)
Glad you liked it!
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!
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)
Good one 👍 Thank you!
Glad you liked it!
Thanks Chandeep for nice tips ..
Always welcome!
This is a good example of how knowing just a little bit of manual coding can give big benefits.
Indeed!
promote headers is great step to reduce in Power query
This video is nothing but AWESOME, ;)..!!!
Glad you liked it!
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!
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!
Thank you
Good tricks
Sir, More steps make File size larger?
Promoted Header was unknown to me. Thanks for sharing this.
pleasure!
Nice tips there.
From the performance point of view, do these tips add any value to my queries?
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!
My best PQ trick is to watch videos from Goodly 😊😊
Glad to hear it!
renaming the columns in M code rather than using an applied step is genius, so simple but wasn't obvious to me
very informative. Can u tell how to unlink the all quires in one step ,once work done ?
finished watching
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.
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?
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
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 - ruclips.net/video/2bzJFrJPg_c/видео.html
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.
You can do that using a list accumulate function
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
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.
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.
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.
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
Is there a performance issue if we reduce steps?
Mostly it's a performance boost not an issue.
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 😂