Determine level within multi level parent child hierarchy

Hi joshua1990 

In the query, add a custom column that calculates the level for each parent-child combination by calling a recursive function. The recursive function will take the current parent-child combination as input and check if the parent is also present in the child column. If so, it will call itself with the new parent-child combination, and increment the level by . Otherwise, it will return the current level.

You can define the function like this:

let
hierarchy = [Parent = "1", Child = "A1", Quantity = 1],
level = 1,
findLevel = (hierarchy, level) =>
let
parent = hierarchy[Parent],
child = hierarchy[Child],
newHierarchy = Table.SelectRows(hierarchy, each [Parent] = child),
newLevel = level + 1
in
if List.Count(newHierarchy) = 0 then
level
else
findLevel(newHierarchy{0}, newLevel)
in
findLevel(hierarchy, level)

Then you can use this function in a custom column, you can call this function by passing the current row to it, and use the result in a custom column. 

= Table.AddColumn(hierarchy, "Level", each findLevel([Parent = [Parent], Child = [Child]], 1))

Finally, you can expand the new column to have it in your table.

Please note, this example is based on the assumption that you have only one Parent-child combination in the table. If there are multiple parent-child combinations, you need to iterate over the table rows, and call the function for each row.

Please let me know if this helps or if you have any other questions.

why the level in your table is 1,2,3,4?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEEYZKsToQboSzEZA0AvPBUk4gvimYD2ZGOJsDSQul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t, Quantity = _t]),
    fx=(t)=>
        let
           a=Source{[Child=t]}?[Parent]?
        in if a=null then 1 else Fx(a)+1,
    Custom1 = Table.AddColumn(Source,"Level",each fx([Parent]))
in
    Custom1