Create a path with the info from other column

[dobregon]

Hi guys, 

I'm looking to create a path with other info than the ids that form the path, let me explain. This is the info that i have as an example

Id	ParentId	Hierarchy	Order
1	NULL	Mountain	1
2	NULL	City	2
3	NULL	Beach	3
4	2	Barcelona	1
5	2	Madrid	2
6	2	Valencia	3
7	3	Barceloneta	1
8	3	Macarella	2
9	3	Macarelleta	3
10	3	Turqueta	4
11	1	Teide	1
12	1	Everest	2
13	1	Pirineos	3
14	1	Kilimanjaro	4

And i can create the path using the path function and i have something like this

Id	ParentId	Hierarchy	Order	Path
1	NULL	Mountain	1	1
2	NULL	City	2	2
3	NULL	Beach	3	3
4	2	Barcelona	1	2|4
5	2	Madrid	2	2|5
6	2	Valencia	3	2|6
7	3	Barceloneta	1	3|7
8	3	Macarella	2	3|8
9	3	Macarelleta	3	3|9
10	3	Turqueta	4	3|10
11	1	Teide	1	1|11
12	1	Everest	2	1|12
13	1	Pirineos	3	1|13
14	1	Kilimanjaro	4	1|14

That i want to have is a similar path format looking all the parents in the hierarchy but form the column with the order, having the result of something like this

Id	ParentId	Hierarchy	Order	Path	OrderPath	Orderpathcorrected
1	NULL	Mountain	1	1	1	1
2	NULL	City	2	2	2	2
3	NULL	Beach	3	3	3	3
4	2	Barcelona	1	2|4	2|1	2.1
5	2	Madrid	2	2|5	2|2	2.2
6	2	Valencia	3	2|6	2|3	2.3
7	3	Barceloneta	1	3|7	3|1	3.1
8	3	Macarella	2	3|8	3|2	3.2
9	3	Macarelleta	3	3|9	3|3	3.3
10	3	Turqueta	4	3|10	3|4	3.4
11	1	Teide	1	1|11	1|1	1.1
12	1	Everest	2	1|12	1|2	1.2
13	1	Pirineos	3	1|13	1|3	1.3
14	1	Kilimanjaro	4	1|14	1|4	1.4

And it should be somethign with the path becasue i dont know how many levels of childs i will have in the future.

It could be possible?
Kind regards!

[v-frfei-msft]

Hi @dobregon ,

For PATH function, The delimiter used to separate the ascendants is the vertical bar, '|'. We can use CONCATENATE to work around here.

Column 2 =
VAR a =
    CONCATENATE ( 'Table'[ParentId], "." & 'Table'[Order] )
RETURN
    IF ( 'Table'[ParentId] = BLANK (), "" & 'Table'[Order], a )

For more details, please check the pbix as attached.

[dobregon]

thanks for the response @v-frfei-msft , i have done this but it only works with hierarchies that can have only one parent or null parent. But if we take another example with more levels of hierarchy this doesnt work (example with a path lenght of 3)

Id	ParentId	Hierarchy	Order	Path	OrderPath	Orderpathcorrected
1	NULL	Expected	1	1	1	1
2	NULL	Incomes	2	2	2	2
3	2	Bike	1	2|3	2|1	2.1
4	2	Motorbike	2	2|4	2|2	2.2
5	2	Losses	3	2|5	2|3	2.3
6	5	Theft	1	2|5|6	2|3|1	2.3.1
7	7	Broken	2	2|5|7	2|3|2	2.3.2

And imagine that the pathlen could be something crazy and cant be do 100 levels of ifs in the powerbi. As the path function is automatic that searh all the hierarchy i'm looking for something similar.

I have created another calculated table with only the info from ids and order

Id	Order
1	1
2	2
3	1
4	2
5	3
6	1
7	2

and i'm looking if it is possible to calculate the column orderpath with a function like "PATHCONTAINS" but check all the rows in the column id and if it find subistute with the column order.

Regards!

[dobregon]

i have moved to the powerquery and try to do the query here, i have done the following m query in order to create the Path column like in DAX

/*Creating the path*/    
     PathColumn=Table.AddColumn(ChangedType, "Path", each let 
     myfunction=(myvalue)=>
                    let
                    mylist=Table.SelectRows(ChangedType,each [ID]=myvalue)[ParentId],
                    result=Text.Combine(mylist)
                    in
                    if result= null or result ="" then "" else if @myfunction(result)=null or @myfunction(result)="" then result else result & "|" & @ myfunction(result)
in
Text.Combine(List.Reverse(List.RemoveItems({[ID]}&{[ParentId]}&Text.Split(myfunction([ParentId]),"|"),{"",null})),"|"))
in
    PathColumn

With that i create a new column with the Path, and now i'm looking to create another custom column that replace each IDs in the path for a list of Order