Forum Discussion
Solved
How to split date rang record into multiple rows in power query
Hi all, I have a big challenge with Employees' days-off table and tried to solve it in power query but I couldn't, the dataset looks as follows: Employee Name Day-off Start Date Day-off End D...
Hi, AhmedBadr_81 I did not rename columns in the end - do that yourself if you want.
let s = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszITU1RcEpMKVLSUTLUNzbQNzIwMgayjfQNYUxjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Day-off Start Date" = _t, #"Day-off End Date" = _t, #"# Days" = _t]), source = Table.TransformColumnTypes(s,{{"Day-off Start Date", type date}, {"Day-off End Date", type date}}), f = (s as date, e as date) => List.Generate( () => [som = s, eom = List.Min({Date.EndOfMonth(s), e}), d = Duration.Days(eom - s) + 1], (x) => x[som] <= e, (x) => [som = Date.AddMonths(Date.StartOfMonth(x[som]), 1), eom = List.Min({Date.EndOfMonth(som), e}), d = Duration.Days(eom - som) + 1] ), a = Table.AddColumn(source, "rec", (x) => f(x[#"Day-off Start Date"], x[#"Day-off End Date"])), expand_list = Table.ExpandListColumn(a, "rec"), expand_rec = Table.ExpandRecordColumn(expand_list, "rec", {"som", "eom", "d"}, {"start", "end", "days"}), z = Table.RemoveColumns(expand_rec,{"Day-off Start Date", "Day-off End Date", "# Days"}) in z
Hi AhmedBadr_81 all columns with dates must be of date type (not datetime). You simply replaced my type transformation step with a ref to your data. Try this
let
s = Excel.CurrentWorkbook(){[Name="NewTest"]}[Content],
source = Table.TransformColumnTypes(s,{{"Day-off Start Date", type date}, {"Day-off End Date", type date}}),
f = (s as date, e as date) =>
List.Generate(
() => [som = s, eom = List.Min({Date.EndOfMonth(s), e}), d = Duration.Days(eom - s) + 1],
(x) => x[som] <= e,
(x) => [som = Date.AddMonths(Date.StartOfMonth(x[som]), 1), eom = List.Min({Date.EndOfMonth(som), e}), d = Duration.Days(eom - som) + 1]
),
a = Table.AddColumn(source, "rec", (x) => f(x[#"Day-off Start Date"], x[#"Day-off End Date"])),
expand_list = Table.ExpandListColumn(a, "rec"),
expand_rec = Table.ExpandRecordColumn(expand_list, "rec", {"som", "eom", "d"}, {"start", "end", "days"}),
z = Table.RemoveColumns(expand_rec,{"Day-off Start Date", "Day-off End Date", "# Days"})
in
zThanks a lot, all it's working 👍
