Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Netrelemo
Helper IV
Helper IV

Build a chain of hierarchical names (not IDs)

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   
1Top level root01|0
2Item 112|1
3Item 213|1
4Item 314|1
5Item 425|2
6Item 546|4
7Item 647|4
8Item 758|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 ? 

1 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]) 

vyiruanmsft_0-1708580542844.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Netrelemo
Helper IV
Helper IV

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"

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]) 

vyiruanmsft_0-1708580542844.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

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

vyiruanmsft_0-1708482958428.png

Method 2: Power Query Editor

1. Create a reference table as below

vyiruanmsft_3-1708483877373.png

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"

vyiruanmsft_4-1708483971840.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.