Forum Discussion
Column Delineators and a Hierarchy
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.
Hi Anonymous ,
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.
5 Replies
Please insert your data as tables.
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 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.
