Column Delineators and a Hierarchy

[KikoNano]

Hi all!

I'm in a bit of a pickle here. I have a set of data, attached below (not inclusive of all data elements). Currently, my data is coming in from Excel like the top table is in the first image. I have a series of dependent drop downs in the excel for end users that follow the hierarchy table on the bottom of the first image. No one on the excel would be able to input 'Hope' in column C if they had not input 'Greg' in L2, if that makes sense.

My problem is cropping up in powerbi. I am able to delineate by row, HOWEVER, I am absolutely stumped about how to get my hierarchy below incorporated. My second image attached includes at the top what my current output looks like, the second is what I want to be getting so I can filter appropriately...I have a lot of matrix visuals so when I try and drill down, everything is wrong. I'd love to be able to handle this in PowerQuery, but will I have to use DAX for this level of complexity? Thank you SO MUCH in advance for any and all help. Apologies for mobile formatting. 

[KikoNano]

Parent	Child	Gchild
Kate	Greg,Kyle	Hope,Lola
Kate	Greg	Hope,Charlie
Amanda	Paul	Harry,Ally

^^ How data is being entered, 

HoParent	Child	Gchild
Kate	Greg	Hope
Kate	Greg	Charlie
Kate	Kyle	Lola
Kate	Kyle	Kat
Amanda	Paul	Harry
Amanda	Paul	Ally
Amanda	Paul	Max
Amanda	Jo	Tori

^^ Hierarchy of values

What My Output Looks like (WRONG)

Parent	Child	Gchild
Kate	Greg	Hope
Kate	Greg	Lola
Kate	Kyle	Hope
Kate	Kyle	Lola
Kate	Greg	Hope
Kate	Greg	Charlie
Amanda	Paul	Harry
Amanda	Paul	Ally

What my output should look like

Parent	Child	GChild
Kate	Greg	Hope
Kate	Kyle	Lola
Kate	Greg	Hope
Kate	Greg	Charlie
Amanda	Paul	Harry
Amanda	Paul	Ally

[Anonymous]

Hi @KikoNano ,

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.

[KikoNano]

Amazing, thank you! This solves my problem. Thanks again!

[Anonymous · Accepted solution]

Hi @KikoNano ,

Thank you for your reply, but when I was reviewing it, I found that there seems to be something wrong with my solution. I have updated the new solution below, please refer to it.

Again, the second table is used.

Please download my attachment for details.

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.

[Omid_Motamedise]

Please insert your data as tables.