Forum Discussion
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
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.
4 Replies
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
Community Champion
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.
Perfect Marcel,
worked like a charm!
