Forum Discussion

pani_victoria's avatar
pani_victoria
Helper III
2 years ago
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 (...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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