Table Transformation

[newhopepdx]

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 resulting table that we import into PBI is represented by the first image. For each account there is AccountId, Name & ParentId.

I trying to find M code that will transform the table adding additional columns representing heirarchical levels: Parent, Child1, Child2, etc. 

Intuitively I know it has to be based on nested list creation... sorting the table on ParentId, taking the first row, creating a list of all the records with that ParentId. Working through that list, recursively doing the same thing, each time creating a child list. Just not sure how to accomplish it.

[BA_Pete · Accepted solution]

Hi @newhopepdx ,

In terms of your data structure as an ouput from Power Query, your data is in the optimal format to be used by the data model.

You should look into the PATH DAX functions in order to display this data how you want to:
https://learn.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax 

Pete

[dufoq3]

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

[ZhangKun]

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

[v-pagayam-msft]

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.

[BA_Pete · Accepted solution]

Hi @newhopepdx ,

In terms of your data structure as an ouput from Power Query, your data is in the optimal format to be used by the data model.

You should look into the PATH DAX functions in order to display this data how you want to:
https://learn.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax 

Pete

[newhopepdx]

Pete,

Thanks to your "Look at the Path function" I was able to easily create a hierarchical column. The next step was to use the hierarchy to create a column describing the full name of the Department & Sections with a colon separators (see image below).

The PathItem() function seemed like the answer, obtaining the value for Name where the first item in the Hierarchy = AccountId. Then cycling through the 2nd, 3rd, and 4th PathItem with the same logic. Concatenate all answers with ":" and I'd have what I need.

Tried:

CALCULATE( MAX([Name]), ALL(Table1), Table1[AccountId] = PATHITEM(Table1[Hierarchy], 1))

but that obviously wasn't it. 

Can you point me in the right direction?

Thx

[BA_Pete]

Apologies, I appear to have lost track of what you're actually trying to achieve here.

Do you just want to be able to add a column to visuals that shows the hierachy path of the item displayed, or are you trying manipulate values and/or slice/filter based on the hierarchy?

Pete

[newhopepdx]

No appology necessary. I initially posted before I'd cleary thought through what I was trying to accomplish.

The end goal is to take data supplied by our software that has the first three columns in the image with my previous post (Name, AccountId & ParentId) and end up with a column like Fullname (Dept 1:Sec B:Sub 1), which we can then easily split into Account levels columns to create a hierarchical slicer ablitity for reports.

Your suggesttion to use the Path functions got me to the Hierarchy column which solved all but the last piece... how to translate the path into names. And I don't seem to be able to find the right way forward.

Thanks!

[BA_Pete · Accepted solution]

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

[newhopepdx]

Thanks to you all! That help has set me on the right path.

Steve