Table Transformation

Hi newhopepdx ,

Thank you for reaching out to the Microsoft Fabric Community Forum about the issue you are encountering.

I tried to create it locally and  providing the insights for your reference. The output shows the hierarchy with each parent account's children organized into Child1, Child2, and Child3.
After loading the data,go to transform and select advanced editor and enter the below code:

let
// Sample source data
Source = Table.FromRecords({
[AccountId = 40, Name = "Dept 1", ParentAccountId = 0],
[AccountId = 178, Name = "Dept 2", ParentAccountId = 0],
[AccountId = 275, Name = "Sec A", ParentAccountId = 40],
[AccountId = 1, Name = "Sec B", ParentAccountId = 40],
[AccountId = 159, Name = "Sub 1", ParentAccountId = 1],
[AccountId = 265, Name = "Sub 2", ParentAccountId = 1],
[AccountId = 105, Name = "Sub-Sub 1", ParentAccountId = 159],
[AccountId = 205, Name = "Sec C", ParentAccountId = 178],
[AccountId = 193, Name = "Sub3", ParentAccountId = 205]
}),

// Create a function to get children based on ParentAccountId
GetChildren = (ParentId as any) as list =>
let
// Get all child rows for the given ParentId
ChildRows = Table.SelectRows(Source, each [ParentAccountId] = ParentId),
// Extract 'Name' of the children and return as a list
ChildNames = ChildRows[Name]
in
ChildNames,

// Add a column to get the children for each account
AddChildrenColumn = Table.AddColumn(Source, "Children", each GetChildren([AccountId])),

// Add columns for Child1, Child2, and Child3
AddChild1Column = Table.AddColumn(AddChildrenColumn, "Child1", each try if List.Count([Children]) > 0 then [Children]{0} else null otherwise null),
AddChild2Column = Table.AddColumn(AddChild1Column, "Child2", each try if List.Count([Children]) > 1 then [Children]{1} else null otherwise null),
AddChild3Column = Table.AddColumn(AddChild2Column, "Child3", each try if List.Count([Children]) > 2 then [Children]{2} else null otherwise null),

// Select the final columns for output
FinalTable = Table.SelectColumns(AddChild3Column,{"AccountId", "Name", "ParentAccountId", "Child1", "Child2", "Child3"})
in
FinalTable

Attached sample pbix file for your reference.

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Regards,
Pallavi.

You can implement this using recursion(fx) or looping(fx2).

let
    DataRecord = Record.FromList(ConvertType[ParentAccountId], ConvertType[AccountId]), 

    fx = (v, rec) =>
        let 
            res = [deep = 1, lst = {v}], 
            f = (v, rec, res) => 
                if Record.HasFields(rec, v) and Record.Field(rec, v) <> "0" then 
                    @f(Record.Field(rec, v), rec, [deep=res[deep] + 1, lst = {Record.Field(rec, v)} & res[lst]])
                else 
                    res
        in 
            f(v, rec, res), 
    
    fx2 = (v, rec) =>
        let 
            a = List.Generate(
                () => v, 
                each Record.HasFields(rec, _), 
                each Record.Field(rec, _)
            )
        in 
            [deep = List.Count(a), lst = List.Reverse(a)],  


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFQ0lFySS0oMQTSBkqxOtFKhuYWUDEjuJiRuSmQHZya7AikTKDqICJOSCKmliCx0iSQlCFEo5kpRMgILmRoABXShakEagOrNYBa4gwSBLoCrNrSGKIaRIFUxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccountId = _t, Name = _t, ParentAccountId = _t]),
    ConvertType = Table.TransformColumnTypes(Source,{{"AccountId", type text}, {"Name", type text}, {"ParentAccountId", type text}}), 
    tbl = Table.AddColumn(ConvertType, "new", each fx2([AccountId], DataRecord)),
    AddCustom1 = Table.AddColumn(tbl, "custom1", each Text.Combine(List.Transform([new][lst], each Text.PadStart(_, 3, "0")), "-")),
    AddCustom2 = Table.AddColumn(AddCustom1, "custom2", each Text.Repeat("    ", [new][deep]) & [Name]),
    SortRow = Table.Sort(AddCustom2,{{"custom1", Order.Ascending}})
in
    SortRow

Hi newhopepdx, similar solution to ZhangKun's

Output

Recursive function version:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFQ0lFySS0oMQTSBkqxOtFKhuYWUDEjuJiRuSmQHZya7AikTKDqICJOSCKmliCx0iSQlCFEo5kpRMgILmRoABXShakEagOrNYBa4gwSBLoCrNrSGKIaRIFUxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccountId = _t, Name = _t, ParentAccountId = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"AccountId", type text}, {"ParentAccountId", type text}}),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Table.SelectColumns(ChangedType,{"AccountId", "ParentAccountId"})))),
    F = (id, optional lst)=>
            [ a = Record.FieldOrDefault(R, id, 0),
              b = if a = 0 then lst else @F(a, {id} & (lst ?? {}))
            ][b],
    StepBack = ChangedType,
    Ad_Hierarchy = Table.AddColumn(StepBack, "Hierarchy", each F([AccountId]), type list),
    Ad_H = Table.AddColumn(Ad_Hierarchy, "H", each Text.Repeat("#(tab)", (List.Count([Hierarchy])-1)) & [Name], type text),
    Sorted = Table.Sort(Ad_H,{each Text.Combine(List.Transform([Hierarchy], (x)=> Text.PadStart(x, 5, "0"))) })
in
    Sorted

List.Generate version (you should allways prefer List.Generate instead of Recursive function - it is faster and it saves calculation memory)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFQ0lFySS0oMQTSBkqxOtFKhuYWUDEjuJiRuSmQHZya7AikTKDqICJOSCKmliCx0iSQlCFEo5kpRMgILmRoABXShakEagOrNYBa4gwSBLoCrNrSGKIaRIFUxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccountId = _t, Name = _t, ParentAccountId = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"AccountId", type text}, {"ParentAccountId", type text}}),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Table.SelectColumns(ChangedType,{"AccountId", "ParentAccountId"})))),
    F = (id)=>
        List.Skip(List.Reverse(List.Generate(
            ()=> Record.FieldOrDefault(R, id, 0),
            each _ <> 0,
            each Record.FieldOrDefault(R, _, 0)))),
    StepBack = ChangedType,
    Ad_Hierarchy = Table.AddColumn(StepBack, "Hierarchy", each F([AccountId]) & {[AccountId]}, type list),
    Ad_H = Table.AddColumn(Ad_Hierarchy, "H", each Text.Repeat("#(tab)", (List.Count([Hierarchy])-1)) & [Name], type text),
    Sorted = Table.Sort(Ad_H,{each Text.Combine(List.Transform([Hierarchy], (x)=> Text.PadStart(x, 5, "0"))) })
in
    Sorted