cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JIGAR
Resolver III
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 keyCust source keymember numberStatusStart DateEnd Date
1234x1231111paying member1/1/201812/31/2018
1235x1231111non paying member1/1/201912/31/2019
1236x1231111non paying member1/1/20202/1/2020
1237x1231111non member2/2/202012/31/9999
1238x1242222non paying member10/1/201812/31/2018
1239x1242222paying member1/1/201912/31/2019
1240x1242222non paying member1/1/20202/1/2020
1241x1242222non member2/2/202012/31/9999
1242x1253333paying member1/1/201812/31/2018
1243x1253333non member1/1/201912/31/2019
1244x1253333paying member1/1/202012/31/9999

 

Expected Result :

 

customer keyCust source keymember numberStatusStart DateEnd Date
1234x1231111paying member1/1/201812/31/2018
1235x1231111non paying member1/1/20192/1/2020
1237x1231111non member2/2/202012/31/9999
1238x1242222non paying member10/1/201812/31/2018
1239x1242222paying member1/1/201912/31/2019
1240x1242222non paying member1/1/20202/1/2020
1241x1242222non member2/2/202012/31/9999
1242x1253333paying member1/1/201812/31/2018
1243x1253333non member1/1/201912/31/2019
1244x1253333paying member1/1/202012/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
AlB
Super User III
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]}}),
    #"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 

 

SU18_powerbi_badge

 

 

   

 

View solution in original post

2 REPLIES 2
AlB
Super User III
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]}}),
    #"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 

 

SU18_powerbi_badge

 

 

   

 

View solution in original post

mahoneypat
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.