Merge duplicate rows that are next to each other (based on sort)

[Anonymous]

Hi everyone!

I'm trying to find a way to merge duplicate rows based on Customer & Status, and keep only the earliest date. I managed to do that using Table.Buffer, however I also want to merge ONLY the Status rows that are directly next to each other. For example:

Customer	Date	Status
1	01.01.2020	A
1	05.01.2020	A
1	06.01.2020	B
1	08.01.2020	A
1	10.01.2020	B

The result I want would be:

Customer	Date	Status
1	01.01.2020	A
1	06.01.2020	B
1	08.01.2020	A
1	10.01.2020	B

The result I get instead:

Customer	Date	Status
1	01.01.2020	A
1	06.01.2020	B

Any ideas on how to solve it? 🙂

[CNENFRNL · Accepted solution]

Hi, @Anonymous , the 3rd parameter of Table.Group func, i.e. GroupKind.Local, does the trick to your issue. Pls refer to the M code below,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AMiIwMjAyDHUSlWBypsil3YDEnYCSFsgVW1oQGq6lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumns(Source,{{"Date", each Date.From(_, "fr")}}),

    // The most juicy part of the solution
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Status"}, {{"Custom", each Table.Sort(_, {"Date", Order.Ascending}){0}}}, GroupKind.Local),
    // Yes, you're done! Or 99.99% done.

    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Status"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Customer", "Date", "Status"}, {"Customer", "Date", "Status"})
in
    #"Expanded Custom"

[Anonymous]

Totally solved my problem! Thank you!!!

[edhans]

@CNENFRNL - where did you get info on what GroupKind.Local is doing? The M documentation is useless here. 

It seems to only group if the row above/below is the same. 

So 

would become below, where it only grouped the pair of 4's next to each other on rows 4 and 5. But I'd like to understand more about it before I rely on it. 

And while I'm here, I'm going to ask about the the 7th parameter keyEqualityComparers  in Table.NestedJoin. I've never found an answer as to what that does, and wondering if you have any clue? 😁

[CNENFRNL]

Hi, @edhans , I stumbled onto this blog in this regard,

https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

Indeed, M is sooo... poorly documented by MS; official docs are next to useless! In most cases, we have to google those scattered caviar of M functions. Quite some of them are very powerful; but we can't make the best of them as MS didn't provide detailed, definitive guide on them.

[lbendlin]

Here is the original article that @ImkeF  referenced.

https://web.archive.org/web/20180430162358/https://pqfans.com/836.html

Seems to be very similar to what Partition Over does in TSQL.

[ImkeF]

Hi @edhans ,

I find this a very good article explaining it: https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

Also, there is a 5th element in the function that can come in very handy sometimes: https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

[Anonymous]

The first article is great!

[lbendlin]

Create a Conditional column that checks if the status in the current row is the same as the status in the previous row. Then filter based on that column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AMiIwMjAyDHUSlWBypsil3YDEnYCSFsgVW1oQGq6lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Status = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Check", each if [Index]=0 then false else [Status]=#"Added Index"[Status]{[Index]-1}),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Check] = false))
in
    #"Filtered Rows"

[Greg_Deckler]

@Anonymous I have an idea, call in @ImkeF and @edhans