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 IPete,
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:
but that obviously wasn't it.
Can you point me in the right direction?
Thx
Super User
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
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