5 Tricks to Reduce Steps in Power Query

Рет қаралды 33,566

Күн бұрын

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 KZfaq 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 KZfaq Channel Below
kzfaq.info/love/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

Пікірлер: 74

@cristian.angyal 3 жыл бұрын

Great video Chandeep, very useful! Thanks for the shout-out! Means a lot!

@Smantkar Ай бұрын

explanation is too good. looking for more video's like this

@natam1595 3 жыл бұрын

Thanks, very useful! I never realised that I should use more parameters in functions, this changes my thinking. Time to prune my queries :)

@sanjeevsoni6793 3 жыл бұрын

Last one is the best... Thanks for consolidating the 💡. We will definitely use. Your are excellent 👏👌 in marking the things simple.

@jimfitch 3 жыл бұрын

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.

@frankschadler9407 2 жыл бұрын

Thank you. Another option for the last step: You can combine source and navigation. Copy the {}-part from nav to the source.

@yemiakinwande7039 Жыл бұрын

Fantastic video, Goodly. I use Power Query every day in my day job so this will come in very useful.

@IvanCortinas_ES 2 жыл бұрын

This is insanely good. Thank you Chandeep!

@Chris_in_fremantle 3 жыл бұрын

Great tip re promoting headers. Thanks

@abeerattia4523 2 жыл бұрын

Thanks Chandeep , you make every thing easy for us, Abeer from Egypt

@venkyr81 2 жыл бұрын

Thanks a million for this very useful Video Chandeep!

@vijaykrishnan4151 3 жыл бұрын

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.

@tARasKoni 3 жыл бұрын

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)

@GoodlyChandeep 8 ай бұрын

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

@optimistprime709 3 ай бұрын

Very good tips. Thanks

@askulasaatwik8thc644 3 жыл бұрын

promote headers is great step to reduce in Power query

@sravankumar1767 3 жыл бұрын

Nice Explanation Chandeep...

@OmisileKehindeOlugbenga Жыл бұрын

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.

@GoodlyChandeep Жыл бұрын

Awesome!

@GrainneDuggan_Excel 3 жыл бұрын

Great tips, Chandeep. The promote headers and add date ones are especially helpful! Thanks

@GoodlyChandeep 3 жыл бұрын

Glad it was helpful!

@BorisDK1 7 ай бұрын

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!

@thebhaskarjoshi 2 жыл бұрын

Thanks, very useful!

@alexrosen8762 3 жыл бұрын

Great tricks! Thanks :-)

@ratiram9556 Жыл бұрын

Thanks Chandeep for nice tips ..

@GoodlyChandeep Жыл бұрын

Always welcome!

@b.salmon4194 6 ай бұрын

renaming the columns in M code rather than using an applied step is genius, so simple but wasn't obvious to me

@larmondoflairallen4705 2 жыл бұрын

This is a good example of how knowing just a little bit of manual coding can give big benefits.

@GoodlyChandeep 2 жыл бұрын

Indeed!

@kennethstephani692 2 жыл бұрын

Great video!

@leebecker8255 2 жыл бұрын

Nice! Very handy and simple tricks :)

@GoodlyChandeep 2 жыл бұрын

Glad you liked it!

@sloku8716 3 жыл бұрын

This video is nothing but AWESOME, ;)..!!!

@GoodlyChandeep 3 жыл бұрын

Glad you liked it!

@shraddhamane2385 2 жыл бұрын

very informative. Can u tell how to unlink the all quires in one step ,once work done ?

@sharmarudra Жыл бұрын

Promoted Header was unknown to me. Thanks for sharing this.

@GoodlyChandeep Жыл бұрын

pleasure!

@shabbirkanchwala-abwaab6263 3 жыл бұрын

Thank you Good tricks Sir, More steps make File size larger?

@mnowako 3 жыл бұрын

Good one 👍 Thank you!

@GoodlyChandeep 3 жыл бұрын

Glad you liked it!

@juanm555 Жыл бұрын

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

@GeorgeMurga 2 жыл бұрын

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.

@GoodlyChandeep 2 жыл бұрын

Sure would do!

@chaudhrypankaj Жыл бұрын

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.

@txreal2 Жыл бұрын

My best PQ trick is to watch videos from Goodly 😊😊

@GoodlyChandeep Жыл бұрын

Glad to hear it!

@Lotaristo Жыл бұрын

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.

@barttitulaerexcelbart9400 3 жыл бұрын

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...

@GoodlyChandeep 3 жыл бұрын

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!

@pakipower 10 ай бұрын

Nice tips there. From the performance point of view, do these tips add any value to my queries?

@sandipansarkar9211 7 ай бұрын

finished watching

@thebhaskarjoshi 2 жыл бұрын

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.

@excelemployeeleavetracker1274 3 жыл бұрын

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!

@barttitulaerexcelbart9400 3 жыл бұрын

??? You can just modify the query and add any column you want?

@durbhamurthy2151 3 жыл бұрын

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?

@excel-in-g 3 жыл бұрын

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.

@GoodlyChandeep 3 жыл бұрын

- 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!

@excel-in-g 3 жыл бұрын

@@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.

@mukeshkadkol4759 Жыл бұрын

Do you have early morning live classes ??

@aaronmiller544 11 ай бұрын

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.

@GoodlyChandeep 11 ай бұрын

You can do that using a list accumulate function

@krishnakishorepeddisetti4387 2 жыл бұрын

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

@RonDavidowicz 3 жыл бұрын

Very interesting video for someone just getting started. Question: What is the benefit of reducing steps?

@GoodlyChandeep 3 жыл бұрын

See this - kzfaq.info/get/bejne/aMiqfamjr7bLj5c.html

@zdzislawkes Жыл бұрын

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!!!

@GoodlyChandeep Жыл бұрын

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

@ravpatodia 9 ай бұрын

Need to get in touch with you

@diptisharma9900 2 жыл бұрын

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.

@savagesick12 Жыл бұрын

Is there a performance issue if we reduce steps?

@GoodlyChandeep Жыл бұрын

Mostly it's a performance boost not an issue.

@agg4390 2 жыл бұрын

1:45

@notesfromleisa-land Жыл бұрын

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.

@MoodyPremi 2 жыл бұрын

पहप्पे, आपके विडिओ बहुत कामके हैं , पर यार पीछे बाजा क्यों बजते हो , बड़ा distracting होता है

@stevecassell 2 ай бұрын

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 😂