Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello! Hopefully someone can help me!
I have a table of absence start and end dates (300k rows). Where absences are consecutive (previous end date is the day before the next start date), I want to capture the first of the consecutive dates in that series as Consecutive Abs Start. It could be any number of absences in a consecutive series. Please see the desired result below. I want to do this in PowerQuery M (instead of PowerBI DAX front end) so I can use the result further in my transformations.
If anyone can help me that would be great! Thanks. Mike
Person | Abs Start | Abs End | Consecutive Abs Start (desired result) |
20000001 | 01.01.2024 | 02.01.2024 | 01.01.2024 |
20000001 | 15.01.2024 | 17.01.2024 | 15.01.2024 |
20000001 | 18.01.2024 | 19.01.2024 | 15.01.2024 |
20000001 | 20.01.2024 | 20.01.2024 | 15.01.2024 |
20000001 | 21.01.2024 | 21.01.2024 | 15.01.2024 |
20000001 | 22.01.2024 | 22.01.2024 | 15.01.2024 |
20000001 | 23.01.2024 | 23.01.2024 | 15.01.2024 |
20000001 | 01.02.2024 | 01.02.2024 | 01.02.2024 |
20000001 | 15.02.2024 | 15.02.2024 | 15.02.2024 |
20000001 | 16.02.2024 | 16.02.2024 | 15.02.2024 |
20000001 | 01.03.2024 | 01.03.2024 | 01.03.2024 |
Solved! Go to Solution.
Hi @Mike1983,
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddDRCoAgDIXhd/FaYjuW1bOI7/8atgg7jSbe/PDBdK0lyH005SS6XBeC1QIzev4w3YjpHrKD2RkxCDEKz/htFJ6BWfgFFGYlYrYQzIW88bOQySg8q8xqxGxO4aFP9D4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Abs Start" = _t, #"Abs End" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Abs Start", type date}, {"Abs End", type date}}, "sk-SK"),
#"Added Index1" = Table.AddIndexColumn(#"Changed Type", "IndexHelper", 0, 1, Int64.Type),
#"Added Dates" = Table.AddColumn(#"Added Index1", "Dates", each List.Dates([Abs Start], Duration.TotalDays([Abs End] - [Abs Start])+1, #duration(1,0,0,0)), type list),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Dates", "Dates"),
#"Added Index" = Table.AddIndexColumn(#"Expanded Dates", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index",
{"Person", "Dates", "Index"}, { {"All", each _, type table}, {" Consecutive Abs Start", each List.Min([Dates]), type date}},
GroupKind.Local,
(s,c)=> Byte.From(c[Index]-s[Index] <> Duration.Days(c[Dates]-s[Dates]))),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Abs Start", "Abs End", "IndexHelper"}, {"Abs Start", "Abs End", "IndexHelper"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded All", {"IndexHelper"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"IndexHelper", "Dates", "Index"})
in
#"Removed Columns"
Hi @Mike1983,
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddDRCoAgDIXhd/FaYjuW1bOI7/8atgg7jSbe/PDBdK0lyH005SS6XBeC1QIzev4w3YjpHrKD2RkxCDEKz/htFJ6BWfgFFGYlYrYQzIW88bOQySg8q8xqxGxO4aFP9D4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Abs Start" = _t, #"Abs End" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Abs Start", type date}, {"Abs End", type date}}, "sk-SK"),
#"Added Index1" = Table.AddIndexColumn(#"Changed Type", "IndexHelper", 0, 1, Int64.Type),
#"Added Dates" = Table.AddColumn(#"Added Index1", "Dates", each List.Dates([Abs Start], Duration.TotalDays([Abs End] - [Abs Start])+1, #duration(1,0,0,0)), type list),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Dates", "Dates"),
#"Added Index" = Table.AddIndexColumn(#"Expanded Dates", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index",
{"Person", "Dates", "Index"}, { {"All", each _, type table}, {" Consecutive Abs Start", each List.Min([Dates]), type date}},
GroupKind.Local,
(s,c)=> Byte.From(c[Index]-s[Index] <> Duration.Days(c[Dates]-s[Dates]))),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Abs Start", "Abs End", "IndexHelper"}, {"Abs Start", "Abs End", "IndexHelper"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded All", {"IndexHelper"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"IndexHelper", "Dates", "Index"})
in
#"Removed Columns"
Fantastic, thank you. I got there in the end but I used about 10 times as many steps, this is certainly much more elegant.