Forum Discussion
Lookup hierarchical data in Query Editor
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.
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
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
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.