PATH function extract sub-level 2 hierarchy

[witbi]

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 my data (see below). Am starting out with Power BI so if anyone can provide some guidance that would be great.

Thanks in advance

[Anonymous · Accepted solution]

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 · Accepted solution]

@witbi here is the formula for your reference

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

[witbi]

Wow, many thanks again @wdx223_Daniel. This works! I have learned much from your clear and elegant solutions.

[Anonymous · Accepted solution]

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.

[witbi]

Yes @Anonymous an Index makes sense

[lbendlin]

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

[witbi]

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