combine two tables by date condition

[pani_victoria]

Hello colleagues!

I have two tables.

One table with one set of dates (01/01/15 - 07/22/24), the second table with another set of dates (07/15/24-07/23/24).

I want to merge these two tables (combine), but so that the dates from the second table that exist in the first table are excluded. That is, to merge from the second table, only rows with the date 07/23/24 were used.

Please help )

[AlienSx]

table_01 & Table.RemoveMatchingRows(table_02, Table.ToRecords(table_01), {"Date"})

[dufoq3 · Accepted solution]

Hi @pani_victoria, check this:

Result

let
    Table1 = Table.FromList(List.Dates(#date(2015,1,1), Duration.TotalDays(#date(2024,7,22)-#date(2015,1,1))+1, #duration(1,0,0,0)), (x)=> {x}, type table[Date=date]),
    Table2 = Table.FromList(List.Dates(#date(2024,7,15), Duration.TotalDays(#date(2024,7,23)-#date(2024,7,15))+1, #duration(1,0,0,0)), (x)=> {x}, type table[Date=date]),
    SelfMergeLeftAnti = Table.NestedJoin(Table2, {"Date"}, Table1, {"Date"}, "Source", JoinKind.LeftAnti),
    RemovedColumns = Table.RemoveColumns(SelfMergeLeftAnti,{"Source"})
in
    RemovedColumns