Forum Discussion
Solved
Create a column with hierarchy information
Hi there, I have a table: rowNo description totaling rowNo value 1009 Level 0 1030 1009 Level 0 4820 1030 Level 1 1050 1030 Level 1 1060 1050 data 1 ...
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
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
FinalTable
Dear rohit1991 :
Thanks for the code. I'm getting this error message:
Expression.Error: The name 'RecursiveFunction' was not recognized. Is it spelled correctly
Add @ in front. The offending line would have to be:
@RecursiveFunction(CleanedTable, Iteration + 1)
