expand and merge tables (without identical columns)

[Charmaine]

Hi all, I would like to join two tables. or it is actually adding a new expanded column. The situation is as screenshot. 

Any idea how to complete this in Power BI? Thank you 😉 

[v-huizhn-msft · Accepted solution]

Hi @Charmaine,

I reproduce your scenario and get expected result.

Please click "New Table" under Modeling on Home page, type the following formula, you will get expected table.

Result Table = CROSSJOIN(Table1,Table2)

You can download the attachment for more details.

Best Regards,
Angelia

[Ashish_Mathur · Accepted solution]

Hi,

This can be done quite easily in the Query Editor.  You may download my file from here.

[eniX]

Hi,

1) create a new calculated column with a single value (i.e. column "join_key" with value "1" for all rows) in both tables

2) merge tables on this column

3) expand second table

4) delete join_key column(s)

eniX.

[Charmaine]

Hi eniX, thanks for your guidance. but the error as below shown. I have tried to remove duplicates..

[v-huizhn-msft · Accepted solution]

Hi @Charmaine,

I reproduce your scenario and get expected result.

Please click "New Table" under Modeling on Home page, type the following formula, you will get expected table.

Result Table = CROSSJOIN(Table1,Table2)

You can download the attachment for more details.

Best Regards,
Angelia

[eniX]

Just a shot in the dark but I would guess you merged in the same query, not in the separate one. Your existing relationsship turns then from m:1 to m:n, so to an M2M (many-to-many) relationship, which is not possible in Power BI. A common workaround for this issue is to create a so called bridge table (or use an existing one).

Try to use table1 or table2 in your first post as the bridge table (I don't know which column you use for the relationship)