Advanced Grouping Tricks In Power Query

[@GoodlyChandeep]

⬇ 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]

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]

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

[@Bhavik_Khatri]

Mark, your insights have motivated me to begin learning LAMBA. This will enable online spreadsheet functionality exceeding the capabilities of VBA custom functions. Thank you.

[@rodriguesfernand]

Great !! Thank so much, @Chandeep !!

[@BicycleFriends]

I love the "Grouped Rows" and Totals example.

[@alexbarbucristi]

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.

[@sarveshdeshpande1772]

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 ❤

[@Hello-bn2yc]

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

[@carlosdias9718]

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]

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

[@jimgleason4040]

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

[@maciejkaron7479]

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

[@anillpatel]

Excellent Presentation. Very useful Grouping techniques.

[@hilaryb4842]

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?

[@shashipaul6279]

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

[@oscarmendez-roca9181]

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

[@fzm280579]

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

[@alexbarbucristi]

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]

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

[@FsoOmar]

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

[@IvanCortinas_ES]

Terribly amazing. Thank you Chandeep!

[@karimallahwala7022]

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

[@shubhabratadey]

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

[@rosicalp]

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

[@obaidulbau]

Wonderful

[@eslamfahmy87]

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 😊

[@dharmmu]

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

[@whizzohk]

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

[@captvo]

Amazing and productive tricks! Thank you!

[@sabbasachisaha4422]

All three are mind-blowing

[@eslamfahmy87]

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

[@lar5091]

All are Awesome.. Thanks for sharing

[@alexrosen8762]

Super super useful tutorial. Thanks a lot

[@chiragdabhi4929]

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

[@sybarix]

Thanks bro, always learning something from you

[@dimmitrio]

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.

[@shri_420]

Thank you ❤

[@sumardjo]

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

[@odallamico]

Hey Mr. Extremely amazing tricks in PQ

[@chrism9037]

Awesome Chandeep!!

[@AnbarasuAnnamalai]

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?

[@BobbySox-i9q]

No CHAR(10) is DAX you need to use carriage return and line feed combination which in ‘M’ is "#(cr)#(lf)"

[@BobbySox-i9q]

"#(cr)#(lf)"

[@anbua9792]

@@BobbySox-i9q Thank you very much for your assistance. This information has proven to be incredibly valuable in enhancing my work performance.

[@dilipinamdar5523]

Nice Thank You🙏

[@ОлегПетров-ю4п]

Thank you very much. You are very cool!

[@윤희순-l7l]

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

[@datawizibility]

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]

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

[@vinothkumarmanoharan2317]

Awesome one

[@sanju4295]

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?

[@laionegan]

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

[@sujithkamath]

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]

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

[@sujithkamath]

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

[@JorgePerez-bu4ph]

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]

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

[@JorgePerez-bu4ph]

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

[@itikshamittal]

I liked the first one 😊

[@estanopossible9561]

All 3 tricks are good

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

[@PritpalSingh-g6h]

bro where do you get those parnas you were? thanks

[@GoodlyChandeep]

outside Bangla Sahib Gurdwara

[@ajieapen]

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

[@williamthedataprof]

pretty damn awesome indeed 🙂

[@TheAlwaysGoel]

Can I combine numberic. Values

[@darwinmercado7943]

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]

His name is Chandeep, not Goodly !

[@raitup00]

The 1st trick I used it a LOT

[@rogeriodornellas2115]

Master M !😎

[@walidjaballah9005]

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

[@brij26579]

🤟 Rocks 🤟

[@aparnapandravada5073]

It looks too much tough for me to remember this 😮

[@shwetkumar1719]

Hello sir, I have start date and end date how to get week and month from it and how can I show it on report for week

[@fewlixmax]

Two queries in saparate Excel save. how to pic data another excel from previous save excel

[@fewlixmax]

please help me

[@dimmitrio]

Just copy query by right click and paste in another.

[@Hae3ro]

@@dimmitriodoes this work?

[@avijitaich787]

No words. But being selfish needs help. Having a problem with find and concatenation

[@prabhuji3337]

Take a bow!

[@aniljaggarwal60]

डियर थैंक्स यदि एक से ज्यादा कॉलम का sum करना हो, month year एंड लोकेशन वाइज कैसे करें जल्दी कृपया बताए ।

[@EricaDyson]

Local grouping.

[@gordoncooper5965]

I am now looking for new socks.

[@Acheiropoietos]

Where are my socks? 🤯😂

[@GoodlyChandeep]

Oops.. did I blow them away😂 Sorry

[@dram3281]

आप हिंदी में वीडियो बना दो सर्च

[@GoodlyChandeep]

www.youtube.com/@desigoodly

[@dram3281]

आपकी अंग्रेजी चैनल की वीडियो की हिंदी वीडियो बना दीजिए सर

[@salahaldeen7924]

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'")