Forum Discussion
Resolver IVHow to merge two rows when date range is in continuation using M Code ?
I am trying to merge the records where date range is in continuation using M Code. Need to know how can I do this ?
I need to do this at dataflow level. Hence not looking for DAX based solutions.
Here is the sample data that I am working on.
| customer key | Cust source key | member number | Status | Start Date | End Date |
| 1234 | x123 | 1111 | paying member | 1/1/2018 | 12/31/2018 |
| 1235 | x123 | 1111 | non paying member | 1/1/2019 | 12/31/2019 |
| 1236 | x123 | 1111 | non paying member | 1/1/2020 | 2/1/2020 |
| 1237 | x123 | 1111 | non member | 2/2/2020 | 12/31/9999 |
| 1238 | x124 | 2222 | non paying member | 10/1/2018 | 12/31/2018 |
| 1239 | x124 | 2222 | paying member | 1/1/2019 | 12/31/2019 |
| 1240 | x124 | 2222 | non paying member | 1/1/2020 | 2/1/2020 |
| 1241 | x124 | 2222 | non member | 2/2/2020 | 12/31/9999 |
| 1242 | x125 | 3333 | paying member | 1/1/2018 | 12/31/2018 |
| 1243 | x125 | 3333 | non member | 1/1/2019 | 12/31/2019 |
| 1244 | x125 | 3333 | paying member | 1/1/2020 | 12/31/9999 |
Expected Result :
| customer key | Cust source key | member number | Status | Start Date | End Date |
| 1234 | x123 | 1111 | paying member | 1/1/2018 | 12/31/2018 |
| 1235 | x123 | 1111 | non paying member | 1/1/2019 | 2/1/2020 |
| 1237 | x123 | 1111 | non member | 2/2/2020 | 12/31/9999 |
| 1238 | x124 | 2222 | non paying member | 10/1/2018 | 12/31/2018 |
| 1239 | x124 | 2222 | paying member | 1/1/2019 | 12/31/2019 |
| 1240 | x124 | 2222 | non paying member | 1/1/2020 | 2/1/2020 |
| 1241 | x124 | 2222 | non member | 2/2/2020 | 12/31/9999 |
| 1242 | x125 | 3333 | paying member | 1/1/2018 | 12/31/2018 |
| 1243 | x125 | 3333 | non member | 1/1/2019 | 12/31/2019 |
| 1244 | x125 | 3333 | paying member | 1/1/2020 | 12/31/9999 |
As shown in the sample data, two rows (row # 2, 3) where member number = 1111 and Status = "non paying member", the start date is adjacent to the end date of previous row.
I need to group such 2 rows by member number and Status and join the date range if its in continuation. (For e.g. Row # 2 in the expected result)
I hope I was able to explain the issue.
Any help on how to do this would be highly appreciated.
Thank you
Hi JIGAR
Hare's an alternative solution that should also work if there are non-contiguous blocks of the same status for the same member. Check out the sample data to understand it. Place the following M code in a blank query to see the steps. Notice the base sample data is a bit different from yours to allow for further testing:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZPBDoMgDIZfxXg2gZZO5VmMhy0xyw6yZaft7QeMThbEAJfW6P/9bX6cphZQqbZrX7baAvbYYu6meZzfN3Nt1mW9LE/3SoBACYNrUajwMHceQQkiIx9j+cjyU80EOkZoRvQVCJS2RW4DYNgF/JQokJVsvmnHwvURNmNgsS6aPPhD7M8IkjXLY4xARsAX4YJE9N/sIuRBjIQJoyZCUmUjZCMk2gXkI9T2sDZcQVeU8r9EzQ2mPpH/WR/uPRRaJ5PPHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"customer key" = _t, #"Cust source key" = _t, #"member number" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer key", Int64.Type}, {"Cust source key", type text}, {"member number", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"customer key", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Cust source key", "member number", "Status"}, {{"Grouped", each _, type table [customer key=nullable number, Cust source key=nullable text, member number=nullable number, Status=nullable text, Start Date=nullable date, End Date=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ =[Grouped], colStart_ = inputT_[Start Date], colEnd_ = inputT_[End Date], S1_ = Table.AddIndexColumn(inputT_,"Index",0), S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index]-1}=Date.AddDays([Start Date],-1) otherwise false then null else [Start Date], type date), S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index]+1}=Date.AddDays([End Date], 1) otherwise false then null else [End Date], type date), #"Filled Up" = Table.FillUp(S3_,{"Date2"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null) in #"Filtered Rows"), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"customer key", "Date1", "Date2"}, {"customer key", "Date1", "Date2"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Date1", "Start Date"}, {"Date2", "End Date"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Grouped"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Start Date", type date}, {"End Date", type date}, {"customer key", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"customer key", "Cust source key", "member number", "Status", "Start Date", "End Date"}), #"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"customer key", Order.Ascending}}) in #"Sorted Rows1"The #"Added Custom" step can also be made into a function. That might make the whole thing more legible
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
2 Replies
- mahoneypat
Microsoft Employee
You should be able to do that with the Group By. Click on Group By and then Advanced and group on the non-date columns, and aggregate for Min start and Max end. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. Note the first column throws it off, so the data are not grouped by Customer Key.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVHRDoMgDPwVw7MJtHROvsX4oIlZ9iAue9r+fkXEuCAG7qWF5O56bdcJQE2iFh+uXIDB5TV8n/ZRzdM8Tm/3LUGigta1KPX26OuVfovodrFVQsIcJUyQaAokUHGLod0E7qcCOxMlBqY3N4zAbT3XbQEZKXN1tQMTaZTkJ5U3QjI/walAVn5Cz3Vn1IzC85OO6H/Wl7kp0zqavP8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"customer key" = _t, #"Cust source key" = _t, #"member number" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer key", Int64.Type}, {"Cust source key", type text}, {"member number", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Cust source key", "member number", "Status"}, {{"StartDate", each List.Min([Start Date]), type nullable date}, {"EndDate", each List.Max([End Date]), type nullable date}}) in #"Grouped Rows"Regards,
Pat
- AlB
Community Champion
Hi JIGAR
Hare's an alternative solution that should also work if there are non-contiguous blocks of the same status for the same member. Check out the sample data to understand it. Place the following M code in a blank query to see the steps. Notice the base sample data is a bit different from yours to allow for further testing:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZPBDoMgDIZfxXg2gZZO5VmMhy0xyw6yZaft7QeMThbEAJfW6P/9bX6cphZQqbZrX7baAvbYYu6meZzfN3Nt1mW9LE/3SoBACYNrUajwMHceQQkiIx9j+cjyU80EOkZoRvQVCJS2RW4DYNgF/JQokJVsvmnHwvURNmNgsS6aPPhD7M8IkjXLY4xARsAX4YJE9N/sIuRBjIQJoyZCUmUjZCMk2gXkI9T2sDZcQVeU8r9EzQ2mPpH/WR/uPRRaJ5PPHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"customer key" = _t, #"Cust source key" = _t, #"member number" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer key", Int64.Type}, {"Cust source key", type text}, {"member number", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"customer key", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Cust source key", "member number", "Status"}, {{"Grouped", each _, type table [customer key=nullable number, Cust source key=nullable text, member number=nullable number, Status=nullable text, Start Date=nullable date, End Date=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ =[Grouped], colStart_ = inputT_[Start Date], colEnd_ = inputT_[End Date], S1_ = Table.AddIndexColumn(inputT_,"Index",0), S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index]-1}=Date.AddDays([Start Date],-1) otherwise false then null else [Start Date], type date), S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index]+1}=Date.AddDays([End Date], 1) otherwise false then null else [End Date], type date), #"Filled Up" = Table.FillUp(S3_,{"Date2"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null) in #"Filtered Rows"), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"customer key", "Date1", "Date2"}, {"customer key", "Date1", "Date2"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Date1", "Start Date"}, {"Date2", "End Date"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Grouped"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Start Date", type date}, {"End Date", type date}, {"customer key", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"customer key", "Cust source key", "member number", "Status", "Start Date", "End Date"}), #"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"customer key", Order.Ascending}}) in #"Sorted Rows1"The #"Added Custom" step can also be made into a function. That might make the whole thing more legible
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
