Forum Discussion
Resolver ITable Transformation
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-daxPete
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
Resolver INo 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!
Super User
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