cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skitzo Frequent Visitor
Frequent Visitor

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

    Table1.Description

    ,Table2.Account

from

    Table 1

    join Table 2 on Table2.Account between Table1.AccountFrom and Table1.AccountTo

 

account.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Joining 2 tables with between clause

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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
aoberlander Frequent Visitor
Frequent Visitor

Re: Joining 2 tables with between clause

You could do this with DAX inside of Power BI. You could either add a calculated column on table 1 and add the decription, you could add a calculated column on table 2 and add the account, or you could create a new table and add only account and description to it.

MarcelBeug Super Contributor
Super Contributor

Re: Joining 2 tables with between clause

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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

skitzo Frequent Visitor
Frequent Visitor

Re: Joining 2 tables with between clause

 Perfect Marcel

 

worked like a charm!

smqasim Visitor
Visitor

Re: Joining 2 tables with between clause

Dear Marcel,

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

 

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 54 members 977 guests
Please welcome our newest community members: