Search code examples
rangepowerbipowerquerym

How can I combine (join) ranges from multiple tables?

I don't want an answer based on DAX or Excel formulas.

I have two tables both with Id and cumulative data. Both fields are table independent.

I need to create a new table, table3, which is a join between table1 and the cumulative2 field from table2. The join criteria is to find the first cumulative2 value superior to cumulative1, as shown in example:

enter image description here

Solution

  • I don't think you can do this with a "join" per se, but you can definitely do it with a custom column.

    let
    Source = Table.FromRows({{1,1000},{2,2000},{3,3000},{4,4000}},{"ID1","Cumulative1"}),
    #"Added Custom" = Table.AddColumn(Source, "Cumulative2", (T1) => List.Min(Table.SelectRows(Table2, each [Cumulative2] >= T1[Cumulative1])[Cumulative2]), type number)
in
    #"Added Custom"

    or formatted a bit

    let
    Source = Table.FromRows({{1,1000},
                             {2,2000},
                             {3,3000},
                             {4,4000}},
                            {"ID1","Cumulative1"}
             ),
    #"Added Custom" = Table.AddColumn(Source, "Cumulative2",
                          (T1) => List.Min(
                                      Table.SelectRows(Table2, 
                                          each [Cumulative2] >= T1[Cumulative1]
                                      )[Cumulative2]
                                  ),
                          type number
                      )
in
    #"Added Custom"

    So in each row in Table1, I take Table2 and select just the rows where

    Table2[Cumulative2] >= Table1[Cumulative1]

    and then return only the Cumulative2 column from that table (and a single column is a list).

    Since I want the first value in that list, I use List.Min.

    Edit: It might be a bit cleaner and more efficient with this instead:

    (T1) => List.First(List.Select(Table2[Cumulative2], each _ >= T1[Cumulative1])),