- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Determine level within multi level parent child hierarchy
Hi all!
I have a table that shows me a multi level parent child hierarchy:
Parent | Child | Quantity |
1 | A1 | 1 |
1 | XC2 | 2 |
A1 | B2 | 5 |
B2 | XC7 | 8 |
As of now, I don't know how many levels are distributed in this table.
Furthermore, I don't know the level for each parent-child combination.
How can I calculate both?
In the end, I would like to have this:
Parent | Child | Quantity | Level |
1 | A1 | 1 | 1 |
1 | XC2 | 2 | 2 |
A1 | B2 | 5 | 3 |
B2 | XC7 | 8 | 4 |
How would you do this in Power Query?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@nitishsh91 : Thanks a lot! Since it is multi level, how would you iterate this per row? I don't get it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The function will take parent n child as input n it will have to be added as a new column to give desired result, I believe it should work though still need to try it myself as I have visualised it conceptually the way an algorithm works.
I will try this in detail sometime tomorrow n let you know as I couldn't find time to work out this solution n had marked it for further analysis.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
09-02-2024 03:35 AM | |||
12-01-2022 01:17 AM | |||
06-17-2021 09:29 AM | |||
12-10-2017 11:04 AM | |||
08-22-2024 01:58 PM |