I make Power Query easy for everyone 🦍 | Microsoft MVP | Power BI Consultant | Master Data Transformation 👉 powerquery.how
🔍 I got it wrong... and I'm excited to share what I learned! A few months back, I dove deep into Custom Comparer functions for Power Query's Table.Group. Pretty advanced stuff with some niche applications. Plot twist: After sharing my theory, the M community jumped in with fresh examples. That's when I realized... I had misunderstood how this functionality actually works! 😅 The good news? Thanks to examples from Zoran, Aditya, and other Power Query wizards, I've completely revised my understanding. ✅ The updated article is now live on my website (link in comments) Here's what found: You can group ALL consecutive dates together using just a single Table.Group formula with a custom comparer. 🎯 Visual example: Start with: Base Table (scattered dates) End with: Grouped Table (consecutive dates perfectly organized) Question for you, have you ever used custom comparer functions in Table.Group? What creative solutions have you built? #PowerQuery #Excel #PowerBI
Here's where you can find the revised article: https://gorilla.bi/power-query/table-group-custom-comparers/. The last example now updates with the Table.Group code for the above example.
Doing god‘s work, eh?
Oh, this is nice!
Thanks Rick, Zoranand Adityafor your contribution. In my testing, when using a custom comparer as the 5th argument in the Table.Group function, the value of the 4th argument (GroupKind) seems to make no difference—even though the preview of the table (as shown in the attached picture) shows an error, it still expands correctly. It can also be set to null. Is this accurate, or can you provide an example where setting GroupKind to Local or Global changes the behavior when using a custom comparer? Try this example: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJVitWJVgIyLGEMYzDDCcgygQmZwRgWYIYjXBWmAU7Iys1hDKBJsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, Qty = _t]), Custom1 = Table.Group(Source,"Data",{"Qty",each Table.AddIndexColumn(_,"Index",1,1)},null,(x,y)=>Number.From(y <> "")), ExpandedQty = Table.ExpandTableColumn(Custom1, "Qty", {"Data", "Qty", "Index"}, {"Data.1", "Qty.1", "Index"}) in ExpandedQty
I make Power Query easy for everyone 🦍 | Microsoft MVP | Power BI Consultant | Master Data Transformation 👉 powerquery.how
1moI'll be honest, this took some trial and error to get right! But here's the logic behind the final solution: The Process: + Create an anchor → The Table.Group function starts by marking the first item in each potential group + Check the next item → It moves to the next row and asks: "Should this belong with the previous item?" + Position validation → It compares where this item sits in the original table vs. where it should sit if dates were consecutive + Date sequence check → It generates the expected date range and verifies if the positions match up + Keep or break → If positions align = same group. If not = new group starts. Think of it like dominoes falling in sequence. The comparer checks if each "domino" (date) is exactly where it should be if they were lined up consecutively. The moment one is out of place, it starts a new sequence. This happens automatically as Table.Group processes each row, no complex nested logic needed!