Forum Discussion
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 my data (see below). Am starting out with Power BI so if anyone can provide some guidance that would be great.
Thanks in advance
6 Replies
LOOKUPVALUE is most likely not what you want. Please provide sample data in usable format (not a picture) and show the expected outcome.
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))-1VAR _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
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.Yes Anonymous an Index makes sense
witbi here is the formula for your reference
you just need to change the number which the arrow points.
Wow, many thanks again wdx223_Daniel. This works! I have learned much from your clear and elegant solutions.
