Forum Discussion
Lookup hierarchical data in Query Editor
Hi,
From Folder Path, I succeeded in computing Folder ID (Index column), Depth and Parent Path in M Query Editor in Power BI.
However, I m struggling to compute Look Up column "Parent Folder Id".
I could not find any function similar to LOOKUPVALUE. So, please guide me on how to implement the solution.
| Folder Path | FolderId | Depth | Parent Folder Path | Parent Folder Id |
| \Inbox\ | 1 | 0 | null | -1 |
| \Inbox\Tickets\ | 2 | 1 | \Inbox\ | 1 |
| \Inbox\Tickets\Power BI\ | 3 | 2 | \Inbox\Tickets\ | 2 |
| \Inbox\Tickets\SSRS\ | 4 | 2 | \Inbox\Tickets\ | 2 |
| \Archive\ | 5 | 0 | null | -1 |
| \Archive\Inbox\ | 6 | 1 | \Archive\ | 5 |
| \Archive\Inbox\Tickets\ | 7 | 2 | \Archive\Inbox\ | 6 |
| \Archive\Inbox\Tickets\Power BI\ | 8 | 3 | \Archive\Inbox\Tickets\ | 7 |
| \Archive\Inbox\Tickets\SSRS\ | 9 | 3 | \Archive\Inbox\Tickets\ | 7 |
let
Source = Exchange.Contents("[email protected]"),
Mail1 = Source{[Name="Mail"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Mail1,{"Folder Path"}),
#"Filtered Rows" = Table.SelectRows(Table.Distinct(#"Removed Other Columns"), each Text.StartsWith([Folder Path], "\Inbox\") or Text.StartsWith([Folder Path], "\Archive\Inbox\")),
#"Added FolderId" = Table.AddIndexColumn(#"Filtered Rows", "FolderId", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added FolderId", "Depth", each Text.Length([Folder Path])-2-Text.Length(Text.Replace([Folder Path],"\","")), Int64.Type),
#"Inserted Text Range" = Table.AddColumn(#"Added Custom", "Level", each Text.Middle([Folder Path], 1, Text.Length([Folder Path])-2), type text),
#"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Text Range", "Level", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"Level.0", "Level.1", "Level.2", "Level.3", "Level.4"}),
#"Added Parent Folder" = Table.AddColumn(#"Split Column by Delimiter", "Parent Path", each if [Depth] = 0 then null
else if [Depth] = 1 then "\" & [Level.0]& "\"
else if [Depth] = 2 then "\" & [Level.0]& "\"& [Level.1]& "\"
else if [Depth] = 3 then "\" & [Level.0]& "\"& [Level.1]& "\" & [Level.2]& "\"
else "\" & [Level.0]& "\"& [Level.1]& "\" & [Level.2]& "\"& [Level.3]& "\")
in
#"Added Parent Folder"Thanks in advance for your assistance.
Regards,
Mannu
For an M-solution, you might want to try this one ;-) :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WionxzEvKr4iJUYrVQeKFZCZnp5YU4xQOyC9PLVJw8sSpIDg4KBgu6ViUnJFZlorJR7UbXRjdETjlMVyDUyXMWbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Folder Path" = _t]),
#"Trimmed Text" = Table.TransformColumns(Source,{{"Folder Path", each Text.Trim(_, "\"), type text}}),
#"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 1, 1),
AddedDepth = Table.AddColumn(#"Added Index", "Depth", each List.Count(List.Select(Text.ToList([Folder Path]), (listItem) => listItem="\"))),
AddedParentFolderPath = Table.AddColumn(AddedDepth, "Parent Folder Path", each Text.BeforeDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd}), type text),
SelfMergeForID = Table.NestedJoin(AddedParentFolderPath,{"Parent Folder Path"},AddedParentFolderPath,{"Folder Path"},"AddedParentFolderPath",JoinKind.LeftOuter),
ExpandID = Table.ExpandTableColumn(SelfMergeForID, "AddedParentFolderPath", {"Index"}, {"Index.1"})
in
ExpandIDIt uses a self-merge to retrieve the ID.
Another advantage is that the Parent Path is calculated dynamically, so it will automatically adjust to any depth.
Imke Feldmann
www.TheBIccountant.com -- How to integrate M-code into your solution -- Check out more PBI- learning resources here
Wow!!
Thank you ImkeF. You actually solved 2 problems instead of one !!!
Unfortunately I can only mark it as an answer once ;-)
Thank you for the quick response and the link to M query reference material.
4 Replies
Hmm, my M is a bit enemic compared to folks like ImkeF. If you don't mind doing it in DAX, you have good luck with functions like PATH, PATHITEM and PATHITEMREVERSE.
There is an M trick that I attempted to use in one of my articles to reference a previous row that might apply here:
=if [Index] = 0 then fnSierpinskiInit("0,1") else fnSierpinskiInit(#"Renamed Columns"{[Index]-1}[Sierpinski])You might try something along those lines.
For an M-solution, you might want to try this one ;-) :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WionxzEvKr4iJUYrVQeKFZCZnp5YU4xQOyC9PLVJw8sSpIDg4KBgu6ViUnJFZlorJR7UbXRjdETjlMVyDUyXMWbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Folder Path" = _t]),
#"Trimmed Text" = Table.TransformColumns(Source,{{"Folder Path", each Text.Trim(_, "\"), type text}}),
#"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 1, 1),
AddedDepth = Table.AddColumn(#"Added Index", "Depth", each List.Count(List.Select(Text.ToList([Folder Path]), (listItem) => listItem="\"))),
AddedParentFolderPath = Table.AddColumn(AddedDepth, "Parent Folder Path", each Text.BeforeDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd}), type text),
SelfMergeForID = Table.NestedJoin(AddedParentFolderPath,{"Parent Folder Path"},AddedParentFolderPath,{"Folder Path"},"AddedParentFolderPath",JoinKind.LeftOuter),
ExpandID = Table.ExpandTableColumn(SelfMergeForID, "AddedParentFolderPath", {"Index"}, {"Index.1"})
in
ExpandIDIt uses a self-merge to retrieve the ID.
Another advantage is that the Parent Path is calculated dynamically, so it will automatically adjust to any depth.
Imke Feldmann
www.TheBIccountant.com -- How to integrate M-code into your solution -- Check out more PBI- learning resources here
Wow!!
Thank you ImkeF. You actually solved 2 problems instead of one !!!
Unfortunately I can only mark it as an answer once ;-)
Thank you for the quick response and the link to M query reference material.
Thank you Greg_Deckler for the quick response.
I m anaemic in both DAX and M ;-)
But then, this is my first attempt with everything hierarchical.
Will check on it. thanks
