Custom Comparer Function for Table.Group in Power Query M

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

Просмотров 4,2 тыс.

50% 1

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
ru-vid.com?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

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

29 июн 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@josh_excel 19 дней назад

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.

@ExcelOffTheGrid 20 дней назад

That is... 🤯 So much to take in... will need to watch this a few times.

@BIGorilla 19 дней назад

Glad to read that Mark. The video will be here whenever you're ready :)

@shreedharan.moorthy 20 дней назад

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.

@BIGorilla 20 дней назад

Yes - this is a complex one. For those who want to go a little crazy 😛😛 Enjoy!

@mohammadtawhidulislam4459 20 дней назад

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!

@reyhan532 19 дней назад

Really awesome. Another level.. hope I will see another video about this.

@Maartenmarsje 19 дней назад

Blew my mind. Thanks! I do have to watch this twice to learn it haha.

@raimundojs9547 20 дней назад

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.

@MattClark-de7on 20 дней назад

Great lesson, I love the hidden gems like the GroupKind.Local and use of Comparer functions to further refine groups. Amazing!

@kndeepak 19 дней назад

Really brilliant!!!

@BIGorilla 19 дней назад

Glad you enjoyed it!

@Charlie888 20 дней назад

I really enjoyed looking through your work on this subject.

@BIGorilla 20 дней назад

Glad you enjoyed it!

@dabrain6944 19 дней назад

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

@Dzidza1978 8 дней назад

Brilliant :-), and helpful

@hassanjatta4257 20 дней назад

Awesome!!! Enjoying the power of Excel.

@thierrysouchard471 12 дней назад

Awesome. Thanks

@williamarthur4801 20 дней назад

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

@BIGorilla 20 дней назад

Glad to hear your thoughts later William!

@nikhilkurmi9580 7 дней назад

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.

@boissierepascal5755 20 дней назад

High level, but very interesting topic.

@BIGorilla 20 дней назад

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!

@user-yo2nb4ry7i 20 дней назад

Good!

@mrbartuss1 20 дней назад

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?

@BIGorilla 20 дней назад

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 😁

@TheMoh148 20 дней назад

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 ?

@rrrraaaacccc80 9 дней назад

💯👍

@mdismailhosen8280 19 дней назад

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?

@haydeecastillo4621 20 дней назад

My head is pounding trying to figure out how to use

@BIGorilla 20 дней назад

Is this video helping? :)

@user-lw1do6ds8d 20 дней назад

Hi man it’s Chinese holiday today

@BIGorilla 20 дней назад

Happy holidays. Here’s a fun video!

@ginesc 17 дней назад

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.

@KamranMumtazAhmed 17 дней назад

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.