Forum Discussion
Solved
combine two tables by date condition
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 )
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
2 Replies
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 RemovedColumnstable_01 & Table.RemoveMatchingRows(table_02, Table.ToRecords(table_01), {"Date"})
