Forum Discussion
PATH Partition by
Is there any way to build a path that is to be partitioned by an additional partition column (group by column)
desired result - partitioned by Q
| Q | parent | child | rowNum | path |
| - | ------ | ----- | ------ | ------|
| 1 | 19 | 24 | 1 | 19|24 |
| 1 | 19 | 20 | 2 | 19|20 |
| 1 | 19 | 19 | 3 | 19|19 |
| 2 | 30 | 24 | 4 | 30|24 |
| 2 | 30 | 20 | 5 | 30|20 |
| 2 | 30 | 30 | 6 | 30|30 |
| 2 | 19 | 18 | 7 | 19|18 |
| 2 | 19 | 19 | 8 | 19|19 |
currently getting
sample data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTK0BBJGJkqxOsh8A1Q+kADxjYBMYwOEegTfAJVvjOBD9Fug8YHmxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Q1 = _t, parent = _t, child = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1", Int64.Type}, {"parent", Int64.Type}, {"child", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "rowNum", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Q1", "Q"}})
in
#"Renamed Columns"
Thank you in advance.
For a data like this, a path can be simply built out by doing this
and other path callbacks work perfectly on that "built-out path". I did think it would be so simple. Not sure, if this is going to create any issues later, but for now I am working with this.
6 Replies
To use multi parent hierarchies you need to disambiguate/clone the children. Child 20 needs to be split into 20.1 and 20.2
cf. Parent-Child Hierarchies with multiple parents in Power BI with Power Query (thebiccountant.com)
I am not looking for a power query solution, as long as, each parent-child pair has a corresponding partition by column as it is in my case, a simple dax concatenation of parent, child with '|' would create the path, where all the related path callbacks apply
It doesn't appear that PATH can operate on a subtable, so a workaround is to concatenate Q with parent and child to create a unique identifier for each child. You can then create a path on this concatenation and then undo the concatenation if you wish.
For a data like this, a path can be simply built out by doing this
and other path callbacks work perfectly on that "built-out path". I did think it would be so simple. Not sure, if this is going to create any issues later, but for now I am working with this.
If you only have two levels, then you really don't need the fancy PATH functions. They're more needed for arbitrarily many levels of a ragged hierarchy.
