How to merge two rows when date range is in continuation using M Code ?

[JIGAR]

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

[AlB · Accepted solution]

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 

[mahoneypat]

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