Forum Discussion

skitzo's avatar
skitzo
Frequent Visitor
9 years ago
Solved

Joining 2 tables with between clause

Hi,   does anyone have any idea how to join Table1 and Table2 from picture below to get new table in PowerBI which looks like FinalTable?   The SQL pseudo is looking like this:   Select     Ta...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Based on your example, you don't need Table2 and can create the final table in Power Query with the following code:

     

    let
    Table1 = #table(type table[Description = text, Account from = number, Account to = number],{{"XYZ",1000,1003}}),
    #"Added Custom" = Table.AddColumn(Table1, "Account", each {[Account from]..[Account to]}, type {Int64.Type}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Account from", "Account to"}),
    #"Expanded Account" = Table.ExpandListColumn(#"Removed Columns", "Account")
in
    #"Expanded Account"

    If you only need codes that are present in Table2, you can inner join the result with Table2.

     

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

Based on your example, you don't need Table2 and can create the final table in Power Query with the following code:

 

let
    Table1 = #table(type table[Description = text, Account from = number, Account to = number],{{"XYZ",1000,1003}}),
    #"Added Custom" = Table.AddColumn(Table1, "Account", each {[Account from]..[Account to]}, type {Int64.Type}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Account from", "Account to"}),
    #"Expanded Account" = Table.ExpandListColumn(#"Removed Columns", "Account")
in
    #"Expanded Account"

If you only need codes that are present in Table2, you can inner join the result with Table2.

 

smqasim's avatar
smqasim
Regular Visitor
8 years ago

Dear Marcel,

 I have the same condition but I want use 2nd table as in a filter.