Issues Joining Many to Many Cardinality

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

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.

Thanks so muuch! Anonymous