Advanced Grouping Tricks In Power Query

Рет қаралды 29,308

Күн бұрын

Пікірлер: 85

@GoodlyChandeep 6 ай бұрын

⬇ Download the file - goodly.co.in/advanced-grouping-tricks-power-query 📊 Tackle even the most challenging data-cleaning problems. Check out the M Language course and learn to push beyond the user interface - goodly.co.in/learn-m-powerquery/

@walterstevens8676 2 ай бұрын

Some neat tricks here. But the groupings are being done using the table that you probably will want to use for other things as well, so I usually preface groupings with the creation of a reference table...

@ajinkyashinde1778 6 ай бұрын

You are the boss of power query transformation 💪 ❤️❤️

@BicycleFriends 6 ай бұрын

I love the "Grouped Rows" and Totals example.

@Hello-bn2yc 6 ай бұрын

Man you are just Blessed by God in Power Query...

@alexbarbucristi 6 ай бұрын

The first time that I can say "I already knew all" Thank you so much for all your videos, you are the best at Power Query. The only thing I would do differently is for the last trick I would create 2 columns one with minimum date and one with maximum date and then extract the days to ensure I capture the exact period.

@carlosdias9718 5 ай бұрын

Thanks for the video. Blown away since i always assumed Powerquery was linear and sequencial, suddenly seeing you calling early steps blew my mind. Also the flexibility of the group by is quite amazing, i was not aware. Super useful stuff to use on Dataflows.

@priyeshsanghvi8424 5 ай бұрын

I would appreciate if you can please give an example or some pointer for it ?

@maciejkaron7479 6 ай бұрын

This concatenation example is mind blowing for me. I will definetely use it in my future projects. Great video!

@hilaryb4842 6 ай бұрын

You’re the only one who explains M code in a way I can understand it and I desperately needed this video last week, my guy lol. I had to do grouped running totals and everyone else does it by using a custom function. I attempted the custom function until I felt like my brain was melting but I finally got it to work. Seeing your last solution, which was exactly what I needed, is so simple I could cry 😂 For a spreadsheet that uses sources with 100,000+ rows, is this way more efficient than the custom function?

@jimgleason4040 5 ай бұрын

Chandeep, you are absolutely the best. These are brilliant tricks.

@anillpatel 5 ай бұрын

Excellent Presentation. Very useful Grouping techniques.

@sarveshdeshpande1772 6 ай бұрын

I am speechless to what i saw …. Group kind local was mind blowing 😮 I recently worked on one such scenario , i wish this video should have came up earlier. But no worries thanks Chandeep for this wonderful trick and i will correct my data logic now and make it bit simple ❤

@AnbarasuAnnamalai 4 ай бұрын

I loved all the examples, particularly the Text. Combine grouping. Can a delimiter like CHAR(10) be used here to keep texts in a separate line?

@dharmmu 6 ай бұрын

You are just playing with power query like a pro kid.....👍

@rosicalp 6 ай бұрын

Awesome and helpful as usual ! Thanks a lot! I'm already planning to do some optimizations with the append trick.

@FsoOmar 6 ай бұрын

Awesome tricks! All of them are mind-blowing and new to me. Thanks a lot for your fantastic tutorials.

@chiragdabhi4929 5 ай бұрын

First one is what I'm actually looking for !!

@dimmitrio 5 ай бұрын

Great, thank you Chandeep. Could you please make a video about "Query folding", especially how to overcome transformations that break the folding. One example is next video "Getting Crazy with the Power Query UI" from Guy in a Cube. But they are using UI in Power BI Service. In Desktop you have to use M code. I think tricks with Lists usage will be very interesting.

@whizzohk 6 ай бұрын

You're right my friend, that is pretty damn awesome. And simple when you explain it too! Well done and thank you.

@captvo 5 ай бұрын

Amazing and productive tricks! Thank you!

@shubhabratadey 6 ай бұрын

You are a weird GENIUS who keeps us amazing in every videos...😄 Please keep on sharing such Learnings

@IvanCortinas_ES 6 ай бұрын

Terribly amazing. Thank you Chandeep!

@karimallahwala7022 5 ай бұрын

Great content and very helpful. I can‘t choose the Most helpful one. They are all very very good.Thank you so much

@sumardjo 5 ай бұрын

thanks for your knowledge and special thanks for simple file I try to step by step the youtube video thanks very much

@oscarmendez-roca9181 5 ай бұрын

Great and very useful set of tricks, thanks a lot Chandeep!

@sabbasachisaha4422 6 ай бұрын

All three are mind-blowing

@윤희순-l7l 6 ай бұрын

감사합니다. 한국에서~ (Thank you in Korea)

@eslamfahmy87 5 ай бұрын

Awsome 😊 the group kind it what i need, could you make a full tutorial on group kind types

@eslamfahmy87 5 ай бұрын

Perfect goodly as usual but i think we can use Table.Column functuin to get the concatenated result in Exp1, in the meatime ur solution is the best 😊

@odallamico 5 ай бұрын

Hey Mr. Extremely amazing tricks in PQ

@shashipaul6279 6 ай бұрын

So awesome.... I'm going to use 1st n 3rd ....

@sybarix 6 ай бұрын

Thanks bro, always learning something from you

@cwnmaster Ай бұрын

It would be better (in last Tip - Grouping by Stint) rather than counting the months in a stint, identifying when to when a person was in a particular role (consecutively). It can be done in EXCEL. It should be reliably (and without complication) done in Power Query - A challenge for your enthusiasm.

@alexrosen8762 6 ай бұрын

Super super useful tutorial. Thanks a lot

@laionegan 6 ай бұрын

I have asked on the pbi forum without response ,how to make a distinct count of a specific column using and modifing the group by on power query? I need also folding on db .is it possible? Thanks anyway you are a very good PQ teacher

@lar5091 5 ай бұрын

All are Awesome.. Thanks for sharing

@sanju4295 6 ай бұрын

Thanks alot for the video ❤️ I have a doubt for example i have a data of 30 employees for the last month and i need to add a target but the condition is if the employee has taken 2 leaves then the target will be 0 for those 2 leaves and if employee takes more than 2 leaves then the target for 3rd,4th leave and so on will be 100. Leaves can be either CL or EL. Is it possible using power query?

@estanopossible9561 3 ай бұрын

All 3 tricks are good

@fzm280579 6 ай бұрын

Is it possible to sort the text.combine from the first example? Ascending or by another list?

@alexbarbucristi 6 ай бұрын

Yes, use Text.Combine(List.Sort(List.Distinct([Column Name])), ", ") for ascending and Text.Combine(List.Sort(List.Distinct([Column Name]), Order.Descending), ", ") for descending. It would be interesting to know if one can sort a column by a list. I know it allows reordering columns by a list so... probably someone like Goodly can definitely achieve the sorting too.

@fzm280579 5 ай бұрын

@@alexbarbucristi that's nice. I will try this! I want to order by department names by priority, at first I can add a number in front of the name. Thank you so much!

@ОлегПетров-ю4п 5 ай бұрын

Thank you very much. You are very cool!

@sujithkamath 5 ай бұрын

why is it not a good idea to have totals and subtotals in power query? i have heard this from many but never appreciated the reason. There are situations where i would need to have subtotals by say region of north , south, etc. in your examples which would work great if i use group and append in PQ itself. Thank you Chandeep as always, you are amazing.

@GoodlyChandeep 5 ай бұрын

Because you can always use the SUM function to create a total. Power Query is for preparing data and not the output.

@sujithkamath 5 ай бұрын

@@GoodlyChandeep thank you chandeep. I had been able to use PQ to create output reports with conditional formatting to look better than a simple output table. In those cases these totals and specially subtotals showing randomly in between rows depending on data, works amazing. I get your point though in general, totals should be avoided 👍👍

@datawizibility 5 ай бұрын

Great Video as usual from you dear Chandeep ♥....we could have date range and period as output for the last trick. Branch manager from so to so date and period 💡

@GoodlyChandeep 5 ай бұрын

Yes we can, we'll have to modify the MCode slightly for extracting the date range.

@raitup00 5 ай бұрын

The 1st trick I used it a LOT

@ajieapen 6 ай бұрын

The last one is quite similar to the problem that we worked together a couple of years ago... :)

@JorgePerez-bu4ph 6 ай бұрын

Thanks Goody! Very helpful. Silly question: Is there any way to add a related column from another table in Power Query M? I said it is a silly question because I know it can be achieved in DAX, but I have no idea if that can be done in Power Query. I have Table1 and Table2 both with a common Column [A] I need to copy column [B] from Table2 to Table1... can be done in M?

@alexbarbucristi 6 ай бұрын

Merge Query does this. Is on the Hime tab on the menu ribbon. Explore the 6 kinds of Join it offers.

@JorgePerez-bu4ph 6 ай бұрын

@@alexbarbucristi I have been using Power BI for two years and I did not know that option existed. I tried it and it worked perfectly. Power BI is full of surprises 🙂 Thank you!

@walidjaballah9005 Ай бұрын

What is the difference between grouping and pivot in power query ?

@dilipinamdar5523 6 ай бұрын

Nice Thank You🙏

@chrism9037 6 ай бұрын

Awesome Chandeep!!

@itikshamittal 5 ай бұрын

I liked the first one 😊

@shri_420 5 ай бұрын

Thank you ❤

@salahaldeen7924 6 ай бұрын

love it, I tried it in google sheets query(HSTACK(filter(QUERY(A2:D,"select Col1, Col3, sum(Col4) group by Col1,Col3"),choosecols(QUERY(A2:D,"select Col1, Col3, sum(Col4) group by Col1,Col3"),1)""),byrow(ArrayFormula(byrow(UNIQUE(TOCOL(A2:A&C2:C)),LAMBDA(key,key))),LAMBDA(key,textjoin(", ",true,unique(filter(B2:B,A2:A&C2:C=key)))))),"select * label Col1 'Year', Col2 'Co. Name',Col3 'Sales total', Col4 'Sales person'")

@vinothkumarmanoharan2317 6 ай бұрын

Awesome one

@darwinmercado7943 5 ай бұрын

Hi Goodly , How about grouping by year and put a subtotal of each group year for Sales and Profit skip or blank row after the subtotal then Total at the end. how can be done on your video Thanks

@romulusmilea2747 5 ай бұрын

His name is Chandeep, not Goodly !