Forum Discussion

newhopepdx's avatar
newhopepdx
Icon for Resolver I rankResolver I
1 year ago
Solved

Table Transformation

Accounts in a piece of software we use can be created in a parent child scheme and visually represented (on the page we make a selection from and add to the list) like the second image below. The res...
Need Help
  • BA_Pete's avatar
    BA_Pete
    1 year ago

     

    Ah, ok. So you're essentially trying to build a dimension(ish) table.

    In that case, you can add level columns like this:

     

    Level_1 = 
VAR __path = PATH(Table1[AccountId], Table1[ParentAccountId])
RETURN
CALCULATE(
    MAX(Table1[Name]),
    FILTER(
        Table1,
        Table1[AccountId] = VALUE(PATHITEM(__path, 1))
    )
)

     

    Unfortunately you'll need to create one for each level of your hierarchy, but I think it does what you're after.

     

    Output:

     

    Pete

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

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