Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey there,
I'm new to Power Query, so I hope someone can help me with this query.
I have a dataset with ID, Status, Start Date and End Date. There are multiple rows for each ID with different start and end dates. Here's a sample of my dataset.
ID | Status | Start Date | End Date |
1 | A | 01/04/2015 | 28/05/2015 |
1 | A | 28/05/2015 | 15/06/2016 |
1 | B | 15/06/2016 | 19/06/2016 |
1 | B | 19/06/2016 | 31/07/2016 |
1 | B | 31/07/2016 | |
2 | B | 01/03/2017 | 03/06/2018 |
2 | A | 03/06/2018 | 07/08/2018 |
2 | A | 07/08/2018 | 31/12/2018 |
2 | C | 31/12/2018 | 01/09/2019 |
2 | C | 01/09/2019 | 03/05/2020 |
2 | A | 03/05/2020 |
|
3 | C | 01/02/2020 | 03/05/2020 |
3 | C | 03/05/2020 | 05/08/2020 |
I want to group consecutive rows (End Date same as the Start Date of next row) with same status for each ID and take the oldest Start Date and the most recent End Date. Here is the the output I'm looking for.
ID | Status | Start Date | End Date |
1 | A | 01/04/2015 | 15/06/2016 |
1 | B | 15/06/2016 | |
2 | B | 01/03/2017 | 03/06/2018 |
2 | A | 03/06/2018 | 31/12/2018 |
2 | C | 31/12/2018 | 03/05/2020 |
2 | A | 03/05/2020 | |
3 | C | 01/02/2020 | 05/08/2020 |
Is this possible?
Many Thanks for your help.
Solved! Go to Solution.
Hi @Anonymous ,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDoUgDEW3Yjom6YeHwFDfMoz734YtEKXRAYN7esItHAcwBNj0ECP9UIiTBilIqYczPM6EA3BCWi2st7N7rKF+O3VyohbntzPhAEsbyRjZqtFG2UIcd5Xb2TzWkJHKh/Pg1sfinb/HvbhaqM6ZcC+2PxJ67TNwe855AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Status"}, {{"Start Date", each List.First([Start Date]), type nullable date}, {"End Date", each List.Last([End Date]), type nullable date}},GroupKind.Local
)
in
#"Grouped Rows"
Hi @Anonymous ,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDoUgDEW3Yjom6YeHwFDfMoz734YtEKXRAYN7esItHAcwBNj0ECP9UIiTBilIqYczPM6EA3BCWi2st7N7rKF+O3VyohbntzPhAEsbyRjZqtFG2UIcd5Xb2TzWkJHKh/Pg1sfinb/HvbhaqM6ZcC+2PxJ67TNwe855AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Status"}, {{"Start Date", each List.First([Start Date]), type nullable date}, {"End Date", each List.Last([End Date]), type nullable date}},GroupKind.Local
)
in
#"Grouped Rows"
Hi @Payeras_BI,
Related to the above question, if there is a break in dates for the same ID and status, then that should be kept as a different group.
ID | Status | Start Date | End Date |
1 | A | 01/04/2015 | 28/05/2015 |
1 | A | 28/05/2015 | 15/06/2016 |
1 | A | 01/05/2017 | 31/03/2018 |
1 | A | 01/04/2019 | 03/04/2019 |
1 | B | 15/06/2016 | 19/06/2016 |
1 | B | 19/06/2016 | 31/07/2016 |
1 | B | 31/07/2016 | 10/09/2016 |
1 | A | 10/09/2016 |
For e.g rows 3 and 4 in the table above, should be as in the table below,
ID | Status | Start Date | End Date |
1 | A | 01/04/2015 | 15/06/2016 |
1 | A | 01/05/2017 | 31/03/2018 |
1 | A | 01/04/2019 | 03/04/2019 |
1 | B | 15/06/2016 | 10/09/2016 |
1 | A | 10/09/2016 |
Is this possible at all? Please can you/someone help me with this?
Many Thanks.
Hi @Anonymous ,
In this case try with this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LDsAgCAWv0rA2AWr9Les1jPe/RkHTVsOCxbwMPFoDBge3DDHShSdxEDgzUpjQ3e8ssQMOSFEhbo7eGU4S8AJeIRtndBUF/8Hr1P28QDFddY9nV7LOEssCIRX78xI7OKD3Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "GroupBy", each let
Check1 = try Duration.Days(ChangedType[Start Date]{[Index]} - ChangedType[End Date]{[Index]-1}) otherwise 1,
Check2 = try ChangedType[ID]{[Index]} = ChangedType[ID]{[Index]-1} otherwise false,
Check3 = try ChangedType[Status]{[Index]} = ChangedType[Status]{[Index]-1} otherwise false
in
if Check1 = 0 and Check2 =true and Check3 = true then null else [Start Date], type date),
#"Filled Down" = Table.FillDown(#"Added Custom",{"GroupBy"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"ID", "Status", "GroupBy"}, {{"Start Date", each List.Min([Start Date]), type nullable date}, {"End Date", each List.Max([End Date]), type nullable date}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"GroupBy"})
in
#"Removed Columns"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.