Skip to main content

Power BI

Needs Votes

Denormalize parent-child relationships automatically

Vote (24) Share
Fernando Marçal's profile image

Fernando Marçal on 11 Dec 2015 21:49:11

Given that there is no OOB support for parent-child hierarchies, it would be great to havfe an automatic converter from parent-child to a denormalized structure.

Comments (5)
Fernando Marçal's profile image Profile Picture

Thijs on 05 Jul 2020 23:38:10

RE: Denormalize parent-child relationships automatically

In PowerQuery you can use recursive loop functionality to join and expand with a parent-column. See link below
https://stackoverflow.com/questions/42095863/loop-computation-in-power-query
Starting at the lowest level you can loop a List.Max number of times going up to the highest level.

And if the lowest level starts at different parent-levels a List.Generate helps to make different tables and append them (with a filter per table of the lowest level and it's starting point in the hierarchy)
Link:
https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-loop-through-unknown-number-of-tables/df0af6a9-54b9-4b84-9fb5-92393bc9e742

If this kind of code would be integrated in a tutorial like functionality (like Qlik has) that would be great of course.

Fernando Marçal's profile image Profile Picture

Bix on 05 Jul 2020 23:36:42

RE: Denormalize parent-child relationships automatically

Smartsheet: Enable feature Primary and Child task. Smartsheet connector need to be update to fetch the data in same form after connect with the smartsheet.

Fernando Marçal's profile image Profile Picture

Kevin H on 05 Jul 2020 22:55:44

RE: Denormalize parent-child relationships automatically

Yes! Many of our dimensions are parent/child hierarchies.

I'd like for Power BI to support creating true parent/child hierarchies, in a table. (In addition to the current hierarchy functionality, where you create a hierarchy by picking the set of columns, in order, that constitute the hierarchy.)

Then, all of the visualizations that can use hierarchies, like Matrix, Bar Charts, etc. should just "do the right thing" with parent/child hierarchies.

I think this is the most important feature request out there, right now! It would be such a bigger deal than any of the feature requests that currently have 1,000+ votes.

There have been several copies of this "idea" created, with keywords like "parent/child hierarchy," "self-referencing hierarchy" and "ragged hierarchy." Maybe they could be merged?

Fernando Marçal's profile image Profile Picture

Bryan Swan on 05 Jul 2020 22:44:03

RE: Denormalize parent-child relationships automatically

This is ostensibly an OLAP tool, not one where normalization is predicated generally.

Fernando Marçal's profile image Profile Picture

Ivan Bondarenko on 05 Jul 2020 22:26:02

RE: Denormalize parent-child relationships automatically

Have the same task. Imagine we import two columns Child-Parent from external source.
Then we can go with DAX solution
http://www.daxpatterns.com/parent-child-hierarchies/
but it doesn't give flexibility in number of levels of hierarchy.
Would be good to have
1) native function in Power Query engine that can de-normalize structure, two columns as input - Child, Parent.
2) auto-generated hierarchy in Data Model, in case of new level - it should be added in hierarchy automatically