Forum Discussion
do relations between two table
Hi,
I have a doubt of how can i do the next work in power bi desktop.
I have this two tables and i need to create relation between them.
1st table:
| ID | account range | description |
| 1 | [655789..655795] | sells to factory |
| 2 | 655432|655433|655434 | sells to client |
| 3 | 7568909 | buys to client |
2nd table
| ID | account | ammount |
| 1 | 655790 | 560€ |
| 2 | 655789 | 670€ |
| 3 | 7568909 | 1300€ |
I need to create a relation of account range (table 1) and account (table 2). The account range column can have the 3 next format:
- [655789..655795] This is a range of accounts (655789,655790,655791,655792,655793,655794,655795 are inside this range).
- 655432|655433|655434 only this 3 accounts are inside the group of sells to client.
- 7568909 is the only account of the group who buys to client.
How can i transform the table 1 to do relation with table 2. Any suggestion?
Regards
Anonymous , remove [ and ]
split the columns | into rows
and slit the column with .. into two columns
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Now where the value in null the second column copy the value from first column,
You will range for each column (column 1, column 2)
then ty between join in power query
https://radacad.com/dates-between-merge-join-in-power-query
or crossjoin and filter in dax
filter(crossjoin(Table1,Table2), table2[account] >= Table1[Account1] && table2[account] <= Table1[Account2])
2 Replies
Anonymous , remove [ and ]
split the columns | into rows
and slit the column with .. into two columns
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Now where the value in null the second column copy the value from first column,
You will range for each column (column 1, column 2)
then ty between join in power query
https://radacad.com/dates-between-merge-join-in-power-query
or crossjoin and filter in dax
filter(crossjoin(Table1,Table2), table2[account] >= Table1[Account1] && table2[account] <= Table1[Account2])
Thanks amitchandak,
I understand what you say to me but this example is only for the case that the table one column have a range of accounts separated by .. . What appens when only have one account or 3 specific accounts separated by | the situation in this cases is different?
Regards,