Help grouping rows by consecutive dates and category

[Frefin]

Hello,

I need help with a transformation in Power Query.

I have the following table:

Person ID	Role	Location	Value	Date
1	Project Director	Location1	1.5	31/01/2023
1	Project Director	Location1	1.5	31/03/2023
1	Project Director	Location1	1.5	31/05/2023
1	Project Director	Location1	1.5	30/06/2023
1	Project Director	Location1	1.5	31/07/2023
2	Project Director	Location1	0.5	31/03/2023
2	Project Director	Location1	0.5	30/06/2023
2	Project Director	Location1	0.5	31/07/2023
3	Project Manager	Location1	0.5	31/01/2023
3	Project Manager	Location1	0.5	28/02/2023
3	Project Manager	Location1	0.5	31/03/2023
3	Project Manager	Location1	0.5	30/04/2023
3	Project Manager	Location1	0.5	31/05/2023
3	Project Manager	Location1	0.5	30/06/2023
4	Operations	Location1	0.5	31/01/2023
4	Operations	Location1	0.5	28/02/2023
4	Operations	Location1	0.5	31/03/2023
4	Operations	Location1	0.5	30/04/2023
4	Operations	Location1	0.5	31/05/2023
4	Operations	Location1	0.5	30/06/2023

I need a table that looks like this:

PersonID	Role	Location	Value	Start Date	End Date
1	Project Director	Location1	1.5	31/01/2023	31/01/2023
1	Project Director	Location1	1.5	31/03/2023	31/03/2023
1	Project Director	Location1	1.5	31/05/2023	31/07/2023
2	Project Director	Location1	0.5	31/03/2023	31/03/2023
2	Project Director	Location1	0.5	30/06/2023	31/07/2023
3	Project Manager	Location1	0.5	31/01/2023	30/06/2023
4	Operations	Location1	0.5	31/01/2023	30/06/2023

I'm having issues wrapping my head around this grouping.

I need the following:

1. Group the data based on PersonID, Role, and Location, and keep the Value column for each person. If the value changes we need a new row that does the check in point 2.

2. For each person, group their data from the first month to the last consecutive month. If there are any gaps in the months, only the last known month before the break should be included. Check the next row to see if there are more months for that person and repeat, if not then move to the next person.

I'm not sure if it makes sense explained this way. 

I would appreciate any help.

[Syndicate_Admin]

hi @Syndicate_Admin  i managed to get the date time but i realized my problem statement is slightly. Would you be able to help? i am still not able to get my desired output https://community.fabric.microsoft.com/t5/Power-Query/how-to-group-consecutive-date-time-range-with-... 

[Syndicate_Admin]

hi @Syndicate_Admin this code is really useful! 

can i ask, if i want the date to be in date time format instead how can i amend the code to reflect so?

E.g. start date and end date will be 2023-05-25 9:00PM

[ronrsnfld]

Did you try setting the data type to datetime wherever it was set to date?

[ronrsnfld · Accepted solution]

There may be more efficient methods but you can do a "double grouping"

• Group by "Person ID", "Role", "Location", "Value"
• For each sub-table
  • Add a custom column that extracts only the Year and Month
  • Create a new table that includes all YrMnths from start to finish
  • Merge the tables
    • nulls will show up where the original table is missing a month from the new table
  • Sort the YrMnth column from the All Dates column (will be Column 1)
  • Group by Person ID using "GroupKind.Local"
    • Then delete the null rows
  • Extract the start and end dates

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZHBCsIwDIZfRXoeNk1W9QE8KvM+diijyDys0u39sQ7EThSbXBpI8/Glf9tWGVWpSww338+b4xBTCTG1TqF38xDG57XZ2nSS0WA0ApLqKiZHQs5KONCwE/r2bw7/c/DtfeXcek+WL9uTMu7sRnf1vzHDx/CgAWU2EmApk1pmszJb9gN1ajV3H5epqSTEAuIjv0IH8Yh1aoUOy3a8suoe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person ID" = _t, Role = _t, Location = _t, Value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Person ID", Int64.Type}, {"Role", type text}, {"Location", type text}, 
        {"Value", type number}, {"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person ID", "Role", "Location", "Value"}, {
        {"Consecutives", (t)=>
            let 
                #"YrMnth" = Table.AddColumn(t,"YrMnth", each Date.Year([Date]) * 1000 + Date.Month([Date]),Int64.Type),
                #"All Months" = 
                    Table.FromColumns({
                        List.Numbers(List.Min(#"YrMnth"[YrMnth]), List.Max(#"YrMnth"[YrMnth]) - List.Min(#"YrMnth"[YrMnth])+1)}),
                #"Merge" = Table.Join(#"YrMnth","YrMnth", #"All Months","Column1", JoinKind.RightOuter),
                #"Sort" = Table.Sort(#"Merge",{"Column1", Order.Ascending}),
                #"Remove" = Table.RemoveColumns(#"Sort", {"YrMnth","Column1"}),
                #"Group by Consecutive Dates" = Table.Group(#"Remove", {"Person ID"}, {
                    {"Start Date", each List.Min([Date])},
                    {"End Date", each List.Max([Date])}
                    
                    }, GroupKind.Local),
                #"Remove Null Rows" = Table.SelectRows(#"Group by Consecutive Dates", each [Person ID] <> null)
            in 
                #"Remove Null Rows"}
        }),
    #"Expanded Consecutives" = Table.ExpandTableColumn(#"Grouped Rows", "Consecutives", {"Start Date", "End Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Consecutives",{{"Start Date", type date}, {"End Date", type date}})
in
    #"Changed Type1"

Results from your posted data