Forum Discussion
pani_victoria
Helper III
Helper IIIcombine 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
- dufoq3
Community Champion
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 - AlienSx
Super User
table_01 & Table.RemoveMatchingRows(table_02, Table.ToRecords(table_01), {"Date"})
