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.
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("mannu@corp.com"), 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
Solved! Go to Solution.
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
ExpandID
It 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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.
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
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
ExpandID
It 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |