Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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.
Dear Marcel,
I have the same condition but I want use 2nd table as in a filter.
Perfect Marcel,
worked like a charm!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |