Issues Joining Many to Many Cardinality

[Anonymous]

Hi All, 

I am trying to join the two tables in the snippet below but having error. please can i need advice on how to resolve the error. 

Thank you.

[ManuelBolz · Accepted solution]

Hello @Anonymous,

i recommend that you create a bridge table so that you can establish a "1 to n" and an "n to 1" relationship between your tables.

Code Query BrideTableGLAccountNo:

let
    BudgetGL = Table.SelectColumns(#"Budget GL", {"G_L_Account_No"}),
    Actuals = Table.SelectColumns(#"Actuals XXX", {"G_L_Account_No"}),
    Combined = Table.Combine({BudgetGL, Actuals}),
    RemovedDuplicates = Table.Distinct(Combined)
in
    RemovedDuplicates

Best regards from Germany
Manuel Bolz

If this post helped you, please consider Accept as Solution so other members can find it faster.

🤝Follow me on LinkedIn

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @ManuelBolz  provided, and i want to offer some more information for user to refer to.

hello @Anonymous , as @ManuelBolz  mentioned, you can create a new table as a brigdge table, you can also use calculated table. you can refer to the following solution.

Sample data is the same as you provided.

Create a new table

Bridge = DISTINCT(UNION(VALUES('Budget GL'[G_L_Account]),VALUES(Actual[G_L_Account])))

Then create relationships amont the tables

And you can refer to the following link.

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-dimensions

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks so much @ManuelBolz 

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @ManuelBolz  provided, and i want to offer some more information for user to refer to.

hello @Anonymous , as @ManuelBolz  mentioned, you can create a new table as a brigdge table, you can also use calculated table. you can refer to the following solution.

Sample data is the same as you provided.

Create a new table

Bridge = DISTINCT(UNION(VALUES('Budget GL'[G_L_Account]),VALUES(Actual[G_L_Account])))

Then create relationships amont the tables

And you can refer to the following link.

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-dimensions

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks so muuch! @v-xinruzhu-msft 

[ManuelBolz · Accepted solution]

Hello @Anonymous,

i recommend that you create a bridge table so that you can establish a "1 to n" and an "n to 1" relationship between your tables.

Code Query BrideTableGLAccountNo:

let
    BudgetGL = Table.SelectColumns(#"Budget GL", {"G_L_Account_No"}),
    Actuals = Table.SelectColumns(#"Actuals XXX", {"G_L_Account_No"}),
    Combined = Table.Combine({BudgetGL, Actuals}),
    RemovedDuplicates = Table.Distinct(Combined)
in
    RemovedDuplicates

Best regards from Germany
Manuel Bolz

If this post helped you, please consider Accept as Solution so other members can find it faster.

🤝Follow me on LinkedIn