How to create relationship when there are hierarchy in tables?

[Anonymous]

I have issues with hierarchy to create relationship between "Piston" and "BMW".  Please advice me!

I would like to have table where Id column included "Piston" and "Maker" column "BMW". I like to slice with "Maker".

Data source is SQL Server. I cannot add anything to database so need to do everything in Power BI side. Should I add new column or create new table? Totally need help!

[Mariusz]

Hi @Anonymous 

What is the relationship between BMW and the Piston?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Relationship is 100(Piston)->200->300->1000->10000(BMW).  (case piston is imaginary, but real case table structure is technically identical)

I wish I could do multiple inner joins in SQL Server and provide single View to create table row including 100 and 10000, but I'm no allowed to edit SQL Server in this case.  There would be also problem with number of levels since it may vary with machine parts. "Please advice how to to with Power BI Desktop. I need to have Power BI table including 100 and 10000 in same row.

[Mariusz]

Hi @Anonymous 

Please see the attached file with a solution in Power Query, Tables additional column hierarchyTypeId finds TypeIds highest Hierarchy that can be later joined with your car manufacturer table.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[v-xicai]

Hi @Anonymous ,

>>I need to have Power BI table including 100 and 10000 in same row.

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy

Community Support Team _ Amy

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