Forum Discussion
Merge duplicate rows that are next to each other (based on sort)
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? 🙂
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"
9 Replies
CNENFRNLCommunity Champion
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"- 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.
- Greg_Deckler
- lbendlin
Super User
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"
