Forum Discussion
Create a column with hierarchy information
Hi Powerwoman, another approach:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwsFTSUfJJLUvNUTAAsgwNjEGUUqwOFkkTCyMkSbBCiKQhWKcpPkkzJEmwwpTEkkSwHFgaKmMGlzGCyBhBZKA2g2WMITJAQ2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rowNo = _t, description = _t, #"totaling rowNo" = _t, value = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"rowNo", type text}, {"totaling rowNo", type text}, {"value", Int64.Type}}), R = Function.Invoke(Record.FromList, Table.ToColumns(Table.SelectRows(ChangedType, each not List.Contains({null, ""}, [totaling rowNo]))[[rowNo], [totaling rowNo]])), F = (child, optional lvl)=> let l = lvl ?? 0, a = Record.FieldOrDefault(R, child, 0), // 0 = highest parent (Level 0) b = if a = 0 then l else @F(a, l+1) in b, Ad_Level = Table.AddColumn(ChangedType, "Level", each F([rowNo]), type text) in Ad_Level
To dynamically create a hierarchy level column in Power Query, follow these concise steps:
-
Add Level 0 Column: Create a custom column: if [totaling rowNo] = null then 0 else null
- Propagate Levels: Use a self-join to match rowNo with totaling rowNo. Increment levels dynamically by adding 1 for each match:Go to Merge Queries and match rowNo to totaling rowNo.Expand the table and increment levels for child rows.
-
Fill Down Levels: Fill down the levels using Transform → Fill → Down.
-
Replace Nulls in Levels:Replace remaining null values in the new column with the parent level + 1. This creates a dynamic hierarchy column with levels. Let me know if you'd like detailed steps or the M code!
Dear rohit1991 , thank you for your response. Would be great if you could send me the M Code!
Thank you so much!
Hi Powerwoman ,
let // Step 1: Load the table (replace 'TableName' with your actual table name) Source = TableName, // Step 2: Add an initial Level column AddLevelColumn = Table.AddColumn(Source, "Level", each if [totaling rowNo] = null then 0 else null, Int64.Type), // Step 3: Perform recursive logic using self-joins to propagate levels RecursiveMerge = let MaxIterations = 10, // Safety limit for recursion RecursiveFunction = (CurrentTable, Iteration) => if Iteration > MaxIterations then CurrentTable else let // Merge rowNo with totaling rowNo to find parent-child relationships MergedTable = Table.NestedJoin( CurrentTable, {"rowNo"}, CurrentTable, {"totaling rowNo"}, "Parent", JoinKind.LeftOuter ), // Expand Parent Level and increment levels for child rows ExpandedTable = Table.ExpandTableColumn( MergedTable, "Parent", {"rowNo", "Level"}, {"ParentRowNo", "ParentLevel"} ), // Update levels where they are null UpdatedTable = Table.TransformColumns( ExpandedTable, {"ParentLevel", each if _ = null then null else _ + 1, Int64.Type} ), UpdatedLevel = Table.TransformColumns( UpdatedTable, {"Level", each if _ = null then [ParentLevel] else _, Int64.Type} ), CleanedTable = Table.RemoveColumns(UpdatedLevel, {"ParentRowNo", "ParentLevel"}) in RecursiveFunction(CleanedTable, Iteration + 1) in RecursiveFunction(AddLevelColumn, 1), // Step 4: Clean up unnecessary columns (optional) FinalTable = Table.SelectColumns(RecursiveMerge, {"rowNo", "description", "totaling rowNo", "value", "Level"}) in FinalTableDear rohit1991 :
Thanks for the code. I'm getting this error message:
Expression.Error: The name 'RecursiveFunction' was not recognized. Is it spelled correctlyAdd @ in front. The offending line would have to be:
@RecursiveFunction(CleanedTable, Iteration + 1)
