Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tnachis
Frequent Visitor

Issues Joining Many to Many Cardinality

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. 

 

tnachis_1-1718009450961.png

 

 

Thank you.

3 ACCEPTED SOLUTIONS
ManuelBolz
Responsive Resident
Responsive Resident

Hello @tnachis,

 

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

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

Hi,

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

hello @tnachis , 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])))

 

vxinruzhumsft_0-1718170399510.png

 

Then create relationships amont the tables

vxinruzhumsft_1-1718170439755.png

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-d...

 

 

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.

View solution in original post

4 REPLIES 4
tnachis
Frequent Visitor

Thanks so much @ManuelBolz 

v-xinruzhu-msft
Community Support
Community Support

Hi,

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

hello @tnachis , 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])))

 

vxinruzhumsft_0-1718170399510.png

 

Then create relationships amont the tables

vxinruzhumsft_1-1718170439755.png

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-d...

 

 

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! @v-xinruzhu-msft 

 

ManuelBolz
Responsive Resident
Responsive Resident

Hello @tnachis,

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors