Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 ?
Solved! Go to 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
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])
Best Regards
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
122 | |
109 | |
60 | |
55 |