Forum Discussion
RobertBech
2 years agoNew Member
Merging holiday table with date table
Hi everyone, I have 2 tables: A table of holidays A date table I would like to merge the two tables to get a table that shows, for each day, who is on holiday: Could y...
- 2 years ago
Hi RobertBech, here you go:
Result
let Table_Holidays = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AUiIxMg09AAyozViVZyAslZIOQskeScUfUZmCLJuaDJGSLJgewzNMdiZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Start Date" = _t, #"End Date" = _t]), Table_Dates = Table.FromList(List.Dates(#date(2024,1,1), 20, #duration(1,0,0,0)), (x)=> {x}, type table[Date=date]), ChangedTypeTable_Holidays = Table.TransformColumnTypes(Table_Holidays, {{"Start Date", type date}, {"End Date", type date}}, "sk-SK"), Ad_Dates = Table.AddColumn(ChangedTypeTable_Holidays, "Dates", each List.Dates([Start Date], Duration.TotalDays([End Date]-[Start Date])+1, #duration(1,0,0,0)), type list), GroupedRowsBuffer = Table.Buffer(Table.Group(Ad_Dates, {"Employee"}, {{"Dates", each List.Combine([Dates]), type table}})), TransformTable_Dates = List.Accumulate( GroupedRowsBuffer[Employee], Table_Dates, (s,c)=> Table.AddColumn(s, c, each [ a = List.Combine(Table.SelectRows(GroupedRowsBuffer, (x)=> x[Employee] = c)[Dates]), b = if List.Contains(a, [Date]) then 1 else 0 ][b], Int64.Type) ) in TransformTable_Dates
dufoq3
2 years agoCommunity Champion
Hi RobertBech, here you go:
Result
let
Table_Holidays = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AUiIxMg09AAyozViVZyAslZIOQskeScUfUZmCLJuaDJGSLJgewzNMdiZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Start Date" = _t, #"End Date" = _t]),
Table_Dates = Table.FromList(List.Dates(#date(2024,1,1), 20, #duration(1,0,0,0)), (x)=> {x}, type table[Date=date]),
ChangedTypeTable_Holidays = Table.TransformColumnTypes(Table_Holidays, {{"Start Date", type date}, {"End Date", type date}}, "sk-SK"),
Ad_Dates = Table.AddColumn(ChangedTypeTable_Holidays, "Dates", each List.Dates([Start Date], Duration.TotalDays([End Date]-[Start Date])+1, #duration(1,0,0,0)), type list),
GroupedRowsBuffer = Table.Buffer(Table.Group(Ad_Dates, {"Employee"}, {{"Dates", each List.Combine([Dates]), type table}})),
TransformTable_Dates = List.Accumulate(
GroupedRowsBuffer[Employee],
Table_Dates,
(s,c)=> Table.AddColumn(s, c, each
[ a = List.Combine(Table.SelectRows(GroupedRowsBuffer, (x)=> x[Employee] = c)[Dates]),
b = if List.Contains(a, [Date]) then 1 else 0
][b], Int64.Type) )
in
TransformTable_Dates- RobertBech2 years agoNew Member
Thank you so much !
- dufoq32 years agoCommunity Champion
You're welcome 🙂