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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.