Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
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.
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.
Solved! Go to Solution.
There may be more efficient methods but you can do a "double grouping"
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
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-...
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
Did you try setting the data type to datetime wherever it was set to date?
There may be more efficient methods but you can do a "double grouping"
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
15 | |
13 | |
12 | |
11 |