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 (...
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
table_01 & Table.RemoveMatchingRows(table_02, Table.ToRecords(table_01), {"Date"})