Custom Comparer Function for Table.Group in Power Query M
Вставка
- Опубліковано 4 сер 2024
- The Table.Group function supports custom comparer functions. These allow you to define your own logic for forming groups. In this video you learn how these work and how you can build your own custom comparer function.
WRITTEN ARTICLE:
gorilla.bi/power-query/table-...
I'm one of the authors of 'The Definitive Guide to Power Query M. If you want to improve your M language skills, you can get a copy here: geni.us/ODZl8
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ua-cam.com/users/bigorilla?sub_con...
TIMESTAMPS
00:00 Introduction
00:25 Comparer Functions
02:35 Build a Custom Comparer Function
06:04 Summarize Dates by Quarter
08:47 Multiple Columns Logic
16:53 Simplified Comparers with GroupKind.Local
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#comparerfunctions #groupby #m
The 5th argument is basically a stand-in for the Comparer.Equals function which returns true or false when used with the other comparer functions which require 2 values to compare, and returns -1 for less than, 0 for equals, and 1 for greater than.
The custom function is turning true and false into 1 and 0 to basically trick the Comparer.Equals function to consider "1" a new group, and "0" to represent "equals" and that it therefore belongs in that group. With the ( x, y ) => syntax, the x is the first value from each sub group so you can compare that value and every other value (y) to each other and determine if it belongs in the group or not. If it doesn't belong, then a new group is started.
Different level, and people who are at basic level (learning power query) will find this one difficult to understand. However, people with high level of passion in learning power query will find it fantabulous. I will have to watch this many times to understand , especially the last 10 min. Kudos.
Yes - this is a complex one. For those who want to go a little crazy 😛😛 Enjoy!
That is... 🤯
So much to take in... will need to watch this a few times.
Glad to read that Mark. The video will be here whenever you're ready :)
Excellent!!!
My Takes:
1. We can use a function/ custom function in a cultural argument, and
2. How you use the if statement to manipulate the Case function.
WOW!
Excelent share about GROUP function ,thanks a lot!
Blew my mind. Thanks! I do have to watch this twice to learn it haha.
The power of the Table.Group function is amazing, to say the least. Thank you so much for sharing. Scary at first, but definitely worthwhile the effort to learn. I will need time to digest this.
Great lesson, I love the hidden gems like the GroupKind.Local and use of Comparer functions to further refine groups. Amazing!
Really awesome. Another level.. hope I will see another video about this.
I really enjoyed looking through your work on this subject.
Glad you enjoyed it!
Really brilliant!!!
Glad you enjoyed it!
Thank you for sharing this one. I've been playing around with local grouping. But using the build in Comparer functions is really cool as well. Haven't seen a good explanation for this before. 👌So again, thank you very much!!!
Awesome!!! Enjoying the power of Excel.
Awesome. Thanks
Brilliant :-), and helpful
Good!
awesome
High level, but very interesting topic.
Thanks - it's quite an advanced topic. But there are a lot of different functions that make use of comparers. That also means you can use this custom comparer logic for those. Think of functions like List.Max, List.Contains, Table.Distinct, Text.StartsWith etc.
Hope you find it useful!
I shall be watching this a few times and having a read, comparer's are one of those things I (should not ) ignore, like quite styles . Also now know why Capitals sort before lower case. 😊😊
Glad to hear your thoughts later William!
This was awesome. QQ how can we Compare 3 date columns (X, Y, Z) and prioritize the latest date within the current month, following the order: X, Y, Z.
💯👍
What if we have a three or four column for the grouping? I understand that it's easy to check if IgnCase = 0 then RespCase else IgnCase. What if i have more than 2 columns? In that case how should we do this checking?
17:00 Could we just use Fill Down and then Group By?
It seems to be a quite advanced topic. Can you recommend where to start as a beginner?
Absolutely. If you fill down, you can use the regular grouping operation.
This video is meant as an exercise to understand custom comparers. It’s not the best way for each scenario.
For instance. Query folding does not work with these conditions.
For a beginner, I would recommend reading. Master your data with power query, the book by Ken Puls and Miguel Escobar. It’s great 😁
Many thanks
can you please make a video to explain how to deal with fact table with date_time column represent measurements each 5 min taking into consideration create date & time columns without loosing query folding and how to use date & time dimensions in the visuals ?
Hi man it’s Chinese holiday today
Happy holidays. Here’s a fun video!
My head is pounding trying to figure out how to use
Is this video helping? :)
Excelent as aditionals options for Grouping , , but no useful as "real grouping" cause even it provides columns to be group, they are been mixup in the output wich lead to mistake data. In this case i.e: with the record : Date,shirt it appears within the table outuput : shirt and path mixup. which isnt right.
You can use Date.EndOfQuarter or Date.StartOfQuarter without mentioning the field names like this
= Table.Group(ChangedType, "Date", {"Quarter Sales", each List.Sum([Sales])},0,
(x,y)=> Value.Compare(Date.EndOfQuarter(x), Date.EndOfQuarter(y))).
Just don't wrap up the KEY with curly brackets and you are good to go.
Another thing, you don't need underscore(_) and Field Name to apply any operation on the field col. I assumed you already know this and didn't do it delibretely.
= Table.Group(Source, "Year", {"SalesMan", each Text.Combine([SalesMan],", ")}, GroupKind.Local,
(x,y) => Number.From(y is number))
I just started following you and am a big fan of your PowerQuery.How work. You did an amazing job by making the website, more power to you Rick.
The video is very informative TBH.
Thanks Kamran - appreciate you!