Column Delineators and a Hierarchy

Hi Anonymous ,

Here's the workaround. You need two tables to achieve this.

The codes of the first table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k4sSVXSUXIvSk3X8a7MAbE98gtSdXzycxKVYnVQFMDknDMSi3IyU8HSjrmJeSmJQJmAxNIckILEoqJKHcecnEql2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t, Gchild = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", type text}, {"Gchild", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Child", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Child"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Child", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Parent", "Child"}, {{"allrows", each _, type table [Parent=nullable text, Child=nullable text, Gchild=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Gchild", "index"}, {"Gchild", "index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"allrows"})
in
    #"Removed Columns"

The codes of the second table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k4sSVXSUXIvSk3X8a7MAbE98gtSdXzycxKVYnVQFMDknDMSi3IyU8HSjrmJeSmJQJmAxNIckILEoqJKHcecnEql2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t, Gchild = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", type text}, {"Gchild", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Gchild", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Gchild"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Gchild", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Parent", "Child"}, {{"Count", each _, type table [Parent=nullable text, Child=nullable text, Gchild=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Gchild", "index"}, {"Gchild", "index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Child", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Child"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Child", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type2", {"Parent", "Child"}, Table, {"Parent", "Child"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"index"}, {"index.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Table", "Custom", each if [index.1]=[index] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"index", "index.1", "Custom"})
in
    #"Removed Columns1"

And the second table is the final result.

You can download my attachment for the detailed steps.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.