Forum Discussion

RobertBech's avatar
RobertBech
New Member
2 years ago
Solved

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...
  • dufoq3's avatar
    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