5 Tricks to Reduce Steps in Power Query

Подписаться 89 тыс.

Просмотров 33 тыс.

50% 1

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 RU-vid 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 RU-vid Channel Below
ru-vid.com/show-UCGQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

Наука

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 74

@cristian.angyal 3 года назад

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

@Smantkar 23 дня назад

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

@frankschadler9407 2 года назад

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

@IvanCortinas_ES 2 года назад

This is insanely good. Thank you Chandeep!

@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 :)

@Chris_in_fremantle 3 года назад

Great tip re promoting headers. Thanks

@yemiakinwande7039 Год назад

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

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

@sanjeevsoni6793 3 года назад

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

@venkyr81 2 года назад

Thanks a million for this very useful Video Chandeep!

@optimistprime709 3 месяца назад

Very good tips. Thanks

@abeerattia4523 2 года назад

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

@sravankumar1767 3 года назад

Nice Explanation Chandeep...

@thebhaskarjoshi 2 года назад

Thanks, very useful!

@alexrosen8762 3 года назад

Great tricks! Thanks :-)

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

@vijaykrishnan4151 2 года назад

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.

@kennethstephani692 2 года назад

Great video!

@sharmarudra Год назад

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

@GoodlyChandeep Год назад

pleasure!

@larmondoflairallen4705 2 года назад

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

@GoodlyChandeep 2 года назад

Indeed!

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

@leebecker8255 2 года назад

Nice! Very handy and simple tricks :)

@GoodlyChandeep 2 года назад

Glad you liked it!

@GoodlyChandeep 7 месяцев назад

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

@askulasaatwik8thc644 3 года назад

promote headers is great step to reduce in Power query

@mnowako 3 года назад

Good one 👍 Thank you!

@GoodlyChandeep 3 года назад

Glad you liked it!

@ratiram9556 Год назад

Thanks Chandeep for nice tips ..

@GoodlyChandeep Год назад

Always welcome!

@b.salmon4194 5 месяцев назад

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

@BorisDK1 6 месяцев назад

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!

@sloku8716 3 года назад

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

@GoodlyChandeep 3 года назад

Glad you liked 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!

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

@pakipower 9 месяцев назад

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

@shabbirkanchwala-abwaab6263 3 года назад

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

@txreal2 Год назад

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

@GoodlyChandeep Год назад

Glad to hear it!

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

@shraddhamane2385 2 года назад

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

@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

@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

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

@mukeshkadkol4759 11 месяцев назад

Do you have early morning live classes ??

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

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

@sandipansarkar9211 6 месяцев назад

finished watching

@RonDavidowicz 3 года назад

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

@GoodlyChandeep 3 года назад

See this - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2bzJFrJPg_c.html

@aaronmiller544 10 месяцев назад

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 10 месяцев назад

You can do that using a list accumulate function

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

@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

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

@ravpatodia 9 месяцев назад

Need to get in touch with you

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

@savagesick12 Год назад

Is there a performance issue if we reduce steps?

@GoodlyChandeep Год назад

Mostly it's a performance boost not an issue.

@agg4390 2 года назад

1:45

@MoodyPremi 2 года назад

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

@stevecassell Месяц назад

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 😂