Forum Discussion

witbi's avatar
witbi
Helper I
5 years ago
Solved

PATH function extract sub-level 2 hierarchy

I'm trying to use PATH functions to generate a flat table for filtering. Examples online seem to start with an explicit hierarchy in the table. I'm stuck on extracting the sub-level 2 hierarchy from ...
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello witbi 
    Are you looking for this?
    Yes, try this. For this, you need to add the index column.
    You can easily add the index column in the power query editor.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    witbi here is the formula for your reference

    you just need to change the number which the arrow points.

     

lbendlin's avatar
lbendlin
Super User
5 years ago

LOOKUPVALUE is most likely not what you want. Please provide sample data in usable format (not a picture) and show the expected outcome.

witbi's avatar
witbi
Helper I
5 years ago

Hi Ibendlin,

Column 1-4 is the initial table. As Anonymous  pointed out I was missing the index.

"Expected Hierarchy" is what I am looking to calculate so I can run PATH functions like this to calculate Level 1, Level 2, Level for use in a slicer:

Level 1= LOOKUPVALUE(Expected_result[Description],Expected_result[Index], PATHITEM(Expected_result[Path],1,1))

 

However, I've tried to calculate the column "Calculated Hierarchy" to resemble the "Expected Hierarchy" but it has become overly complicated. I would appreciate any ideas on creating a calculated column.

 

Calculated Hierarchy =
VAR _l1=IF(LEN(Expected_result_test[Ref])=Expected_result_test[Pathlength],Expected_result_test[Index])
VAR _delimiter1=SUBSTITUTE(Expected_result_test[Ref],".","|",1)
VAR _l2_len=SEARCH("|",_delimiter1,,LEN(_delimiter1))-1
VAR _parent=LEFT(_delimiter1,_l2_len)
VAR _index= IF(ISERROR(LOOKUPVALUE(Expected_result_test[Index],Expected_result_test[Ref],_parent)),"",LOOKUPVALUE(Expected_result_test[Index],Expected_result_test[Ref],_parent))
VAR _path=
IF(
ISBLANK(_l1),
CONCATENATE(_index,CONCATENATE("|",Expected_result_test[Index])),
_l1
)
RETURN
_path


Sample data:

Ref,Description,Event,Index,Expected Hierarchy,Level 1,Level 2,Level 3,Calculated Hierarchy
1,Andy,A,1,1,Andy,,,1
1.1,Green,A,2,1|2,Andy,Green,,|1
1.2,Blue,A,3,1|3,Andy,Blue,,|3
1.2.1,Water,A,4,1|3|4,Andy,Blue,Water,|4
2,Bill,A,5,5,Bill,,,5
2.1,Yellow,A,6,5|6,Bill,Yellow,,5|6
2.1.1,Wood,A,7,5|6|7,Bill,Yellow,Wood,5|7
2.1.2,Paper,A,8,5|6|8,Bill,Yellow,Paper,5|8
2.2,Orange,A,9,5|9,Bill,Orange,,5|9
3,Chris,B,10,10,Chris,,,10
3.1,Purple,B,11,10|11,Chris,Purple,,|11
4,Dave,B,12,12,Dave,,,12
4.1,Black,B,13,12|13,Dave,Black,,12|13
4.2,White,B,14,12|14,Dave,White,,12|14
1,Andy,C,15,1,Andy,,,15
1.2,Blue,C,16,1|3,Andy,Blue,,|16
1.2.1,Water,C,17,1|3|4,Andy,Blue,Water,|17
3,Chris,C,18,10,Chris,,,18
3.1,Purple,C,19,10|11,Chris,Purple,,19