Build a chain of hierarchical names (not IDs)

[Netrelemo]

I have this code: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcq7CoAwDIXhVymZO9irvoK7W8iYrRKR4vPbFNLt8P0HEQJ4uORxjT9u7hXpAzYgjxDHOjvfTj9hUjKKi7JRWlSMtMVJ1UhbnrQb1UWHkbYCRD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FolderID = _t, FolderName = _t, ParentFolder = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FolderID", Int64.Type}, {"FolderName", type text}, {"ParentFolder", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "PATH", each 

        let //Define your columns below
            //Source = LiveARMHierarchy,
            c=[FolderID],
            p=[ParentFolder],
            mytable=Source,
            pc="ParentFolder",
            cc="FolderName" 
        in
            let 
                mylist={c} & List.Generate
                    (
                    ()=>[x=0,y=p,w=1],
                    each [w] > 0,
                    each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)],
                    each [y]
                    )
            in
                Text.Combine((List.RemoveItems(List.Transform(mylist,each Text.From(_)),{null,""})),"|")
                )
    
    in #"Added Custom"

Which produces this: 

FolderIDFolderNameParentFolderPATH

FolderID
1	Top level root	0	1|0
2	Item 1	1	2|1
3	Item 2	1	3|1
4	Item 3	1	4|1
5	Item 4	2	5|2
6	Item 5	4	6|4
7	Item 6	4	7|4
8	Item 7	5	8|5

My problem is the last calculaterd column. 

What I am trying to get is the folder names, rather than the folder IDs. 

So, on row #5 it should have something like: "Item 4 | Item 1" showing that item 4 is part of item 1.

Silly question, but .... How do I replace the numbers with words ? 

[Netrelemo]

Thanks for your sample, but it doesn't work.

Looking back, I realise my sample was incorrect.... I want the complete chain right up to top level. So if the child is 10 levels deep, I want all 10 levels. Not only the parent 1 level up. 

Here's my revised sample. The problem I have is the column titled "PathNames"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcq7CoAwDIXhVymZO9irvoK7W8iYrRKR4vPbFNLt8P0HEQJ4uORxjT9u7hXpAzYgjxDHOjvfTj9hUjKKi7JRWlSMtMVJ1UhbnrQb1UWHkbYCRD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, FolderName = _t, Parent = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child", type text}, {"Parent", type text}, {"FolderName", type text}}),
    #"Added PathIDs" = Table.AddColumn(#"Changed Type", "PathIDs", each 

        let //Define your columns below
            //Source = LiveARMHierarchy,
            c=[Child],
            p=[Parent],
            mytable=#"Changed Type",
            pc="Parent",
            cc="Child" 
        in
            let 
                mylist={c} & List.Generate
                    (
                    ()=>[x=0,y=p,w=1],
                    each [w] > 0,
                    each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)],
                    each [y]
                    )
            in
                Text.Combine((List.RemoveItems(List.Transform(mylist,each Text.From(_)),{null,""})),"|")
                ),
    #"Added PathNames" = Table.AddColumn(#"Added PathIDs", "PathNames",  each try let 
            c=[Child],
            p=[Parent],
            mytable=Source,
            pc="Parent",
            cc="FolderName",
            // Use the reference table to get the FolderName
            FolderName = Table.Column(Source, "FolderName"){List.PositionOf(Source[Child], c)}
        in
            let 
                mylist={FolderName} & List.Generate
                    (
                    ()=>[x=0,y=p,w=1],
                    each [w] > 0,
                    each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,pc)=z),cc),y=x{0},w=List.Count(x)],
                    each [y]
                    ),
                // Replace FolderID with FolderName in the PATH
                ReplacedList = List.Transform(mylist, each if _ is number then Table.Column(Source, "FolderName"){List.PositionOf(Source[Child], _)} else _)
            in
                Text.Combine(List.Reverse(ReplacedList), "|") otherwise null)
in
    #"Added PathNames"

[v-yiruan-msft · Accepted solution]

Hi @Netrelemo ,

It will be easier to achieve it by DAX, you can create two calculated column as below:

Parent Folder Name = 
VAR _pfid = 'Query1'[ParentFolder]
RETURN
    IF (
        'Query1'[ParentFolder] = 0,
        'Query1'[FolderName],
        CALCULATE (
            MAX ( 'Query1'[FolderName] ),
            FILTER ( ALL ( 'Query1' ), 'Query1'[FolderID] = _pfid )
        )
    )

PathFolderName = PATH('Query1'[FolderName],Query1[Parent Folder Name]) 

Best Regards

[v-yiruan-msft]

Hi @Netrelemo ,

You can achieve it by the following 2 methods:

Method 1:  Create a calculated column as below to get it:

Column =
VAR _position =
    SEARCH ( "|", 'Query1'[PATH], 1, 0 )
VAR _path = 'Query1'[PATH]
VAR _left =
    CALCULATE (
        MAX ( 'Query1'[FolderName] ),
        FILTER (
            'Query1',
            'Query1'[FolderID]
                = VALUE ( LEFT ( _path, _position - 1 ) )
        )
    )
VAR _right =
    CALCULATE (
        MAX ( 'Query1'[FolderName] ),
        FILTER (
            'Query1',
            'Query1'[FolderID]
                = VALUE ( RIGHT ( _path, LEN ( 'Query1'[PATH] ) - _position ) )
        )
    )
RETURN
    _left & "|" & _right

Method 2: Power Query Editor

1. Create a reference table as below

2. Update the applied codes in Advanced Editor as below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcq7CoAwDIXhVymZO9irvoK7W8iYrRKR4vPbFNLt8P0HEQJ4uORxjT9u7hXpAzYgjxDHOjvfTj9hUjKKi7JRWlSMtMVJ1UhbnrQb1UWHkbYCRD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FolderID = _t, FolderName = _t, ParentFolder = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FolderID", Int64.Type}, {"FolderName", type text}, {"ParentFolder", Int64.Type}}),

 ReferenceTable =  Table.FromColumns({#"Reference Table"[FolderID], #"Reference Table"[FolderName]}, {"FolderID", "FolderName"}),
     
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PATH", each try let 
            c=[FolderID],
            p=[ParentFolder],
            mytable=Source,
            pc="ParentFolder",
            cc="FolderName",
            // Use the reference table to get the FolderName
            FolderName = Table.Column(ReferenceTable, "FolderName"){List.PositionOf(ReferenceTable[FolderID], c)}
        in
            let 
                mylist={FolderName} & List.Generate
                    (
                    ()=>[x=0,y=p,w=1],
                    each [w] > 0,
                    each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,pc)=z),cc),y=x{0},w=List.Count(x)],
                    each [y]
                    ),
                // Replace FolderID with FolderName in the PATH
                ReplacedList = List.Transform(mylist, each if _ is number then Table.Column(ReferenceTable, "FolderName"){List.PositionOf(ReferenceTable[FolderID], _)} else _)
            in
                Text.Combine(List.Reverse(ReplacedList), "|") otherwise null)
in
    #"Added Custom"

Best Regards