Lookup hierarchical data in Query Editor

[Anonymous]

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

[ImkeF · Accepted 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

[Greg_Deckler]

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.

[Anonymous]

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

[ImkeF · Accepted 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

[Anonymous · Accepted solution]

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.