Merging holiday table with date table

[RobertBech]

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 you please help me to find a solution ?
Thank you in advance !

[dufoq3 · Accepted solution]

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

[RobertBech]

Thank you so much ! 

[dufoq3]

You're welcome 🙂