Forum Discussion

newhopepdx's avatar
newhopepdx
Resolver 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

ZhangKun's avatar
ZhangKun
Super User
1 year ago

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