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)
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.
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.
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?
RE: Denormalize parent-child relationships automatically
This is ostensibly an OLAP tool, not one where normalization is predicated generally.
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