cancel
Showing results for
Did you mean:
Resolver III

## How 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

1 ACCEPTED SOLUTION
Super User III

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]}}),
colStart_ = inputT_[Start Date],
colEnd_ = inputT_[End Date],
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 2
Super User III

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]}}),
colStart_ = inputT_[Start Date],
colEnd_ = inputT_[End Date],
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

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements