PATH Partition by

[smpa01]

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"

@AlexisOlson 

Thank you in advance.

[AlexisOlson]

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.

[smpa01 · Accepted solution]

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.

[Anonymous]

Hi @smpa01 ,

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[AlexisOlson]

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. 

[lbendlin]

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)

[smpa01]

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