⬇ 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/
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...
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.
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.
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.
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 ❤
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?
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'")
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.
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
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?
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.
@@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!
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.
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 💡
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 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 👍👍
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 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!
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
⬇ 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/
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...
You are the boss of power query transformation 💪 ❤️❤️
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.
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.
I love the "Grouped Rows" and Totals example.
Man you are just Blessed by God in Power Query...
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.
I would appreciate if you can please give an example or some pointer for it ?
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 ❤
This concatenation example is mind blowing for me. I will definetely use it in my future projects. Great video!
Awesome tricks! All of them are mind-blowing and new to me. Thanks a lot for your fantastic tutorials.
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?
Chandeep, you are absolutely the best. These are brilliant tricks.
Great content and very helpful. I can‘t choose the Most helpful one. They are all very very good.Thank you so much
Great !! Thank so much, @Chandeep !!
You are a weird GENIUS who keeps us amazing in every videos...😄
Please keep on sharing such Learnings
Awesome and helpful as usual ! Thanks a lot!
I'm already planning to do some optimizations with the append trick.
Excellent Presentation.
Very useful Grouping techniques.
You're right my friend, that is pretty damn awesome. And simple when you explain it too! Well done and thank you.
Great and very useful set of tricks, thanks a lot Chandeep!
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 😊
So awesome.... I'm going to use 1st n 3rd ....
Amazing and productive tricks! Thank you!
You are just playing with power query like a pro kid.....👍
Terribly amazing. Thank you Chandeep!
thanks for your knowledge
and special thanks for simple file
I try to step by step the youtube video
thanks very much
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'")
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.
First one is what I'm actually looking for !!
All are Awesome.. Thanks for sharing
Awsome 😊 the group kind it what i need, could you make a full tutorial on group kind types
Thanks bro, always learning something from you
Super super useful tutorial. Thanks a lot
All three are mind-blowing
Hey Mr. Extremely amazing tricks in PQ
Wonderful
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
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?
Thank you very much. You are very cool!
Is it possible to sort the text.combine from the first example?
Ascending or by another list?
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.
@@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!
Awesome Chandeep!!
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.
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?
No CHAR(10) is DAX you need to use carriage return and line feed combination which in ‘M’ is "#(cr)#(lf)"
"#(cr)#(lf)"
@@BobbySox-i9q Thank you very much for your assistance. This information has proven to be incredibly valuable in enhancing my work performance.
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 💡
Yes we can, we'll have to modify the MCode slightly for extracting the date range.
감사합니다. 한국에서~ (Thank you in Korea)
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.
Because you can always use the SUM function to create a total. Power Query is for preparing data and not the output.
@@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 👍👍
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?
Merge Query does this. Is on the Hime tab on the menu ribbon. Explore the 6 kinds of Join it offers.
@@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!
The last one is quite similar to the problem that we worked together a couple of years ago... :)
All 3 tricks are good
Awesome one
Nice Thank You🙏
Thank you ❤
pretty damn awesome indeed 🙂
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
His name is Chandeep, not Goodly !
I liked the first one 😊
Can I combine numberic. Values
The 1st trick I used it a LOT
What is the difference between grouping and pivot in power query ?
bro where do you get those parnas you were? thanks
outside Bangla Sahib Gurdwara
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
Master M !😎
It looks too much tough for me to remember this 😮
Two queries in saparate Excel save. how to pic data another excel from previous save excel
please help me
Just copy query by right click and paste in another.
@@dimmitriodoes this work?
🤟 Rocks 🤟
डियर थैंक्स
यदि एक से ज्यादा कॉलम का sum करना हो, month year एंड लोकेशन वाइज
कैसे करें जल्दी कृपया बताए ।
No words. But being selfish needs help. Having a problem with find and concatenation
Take a bow!
Local grouping.
I am now looking for new socks.
Where are my socks? 🤯😂
Oops.. did I blow them away😂
Sorry
आप हिंदी में वीडियो बना दो सर्च
www.youtube.com/@desigoodly
आपकी अंग्रेजी चैनल की वीडियो की हिंदी वीडियो बना दीजिए सर