do relations between two table

[Anonymous]

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

[amitchandak · Accepted solution]

@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])

[Anonymous]

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,