Group by duplicate column to lookup Hierarchy Table for multiple values

[chem094]

Hi All, 

i am new to PBI and working on some transformation to generate a report.. i need your suggestion to move forward on the below. 

if you see in below table, i have one institution with 3 valid keywords in it, Using keywords list, i mapped each keyword to its category.

But in final category should be "Ëducation" using hierarchy table. So, education should be filled in all the 3 lines as final category column. Hence, all duplicates (Customer & Final category) can be removed at last step . 

Hierarych Table: 

1. Review the Org (not in keyword list)
2. Library
3. Publication
4. Legal Financial
5. Government
6. Education
7. Company
8. Technology
9. Organization
10. Pharma/Medical
11. Research
12. Service

[v-angzheng-msft · Accepted solution]

Hi, @chem094 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVRLT9tAEP4ro5xaCSygUg+9IDeEEImEiEBViXIYbwbvEHvX2l1HMr++s3bCMwltRQ/Jrme++ea9Nze9AcNJXVkT4IRgQqWtnYdf9cHB0VfoW+iTCQ4LuOxEJ9Db60WZHBcuR8MPGNia3u3eTe/TlSYYeYdUwKQVi+HI+MChDgSn1sEZYRE0TG3BqoFL8oROafg0YV19huJeV06YO2u5DO2SnCmjv491EI3+bworhVzXkNbBNxhz8HXGXjMM0UNfU8lKvEixR0bJL1BRkAq1yKbOVuRCA0NXCZPoY/ltWaFpVnQTCj7anoe56Lr/54jj1OwP6siDBq4NL0W/OgbzWj2lfny9QP8gWecsOsxf83TqWYk5btIfzgJcEUolzqyv4Cebe0aTw5jmO90eTeaQ3t1xwRgEGo05SOoXd3CGLmMTGdoCfShL+ynnVKMrseX4MoZ1Qk+pvUxSICNj7FIcL2ndHSb/rmGapMl35IVdPpsmCW5MEmVRu7yB1MxhLNE7xsLDTDEZ1WIu0be93zyxaTJOYiQLbZ2wD8lQ3NepbjwrL7PcOdvNgC76D9Y0QoUZJrHD2ZscJsmpq8uFVPNFEoM4rs6qOMk+CJXI4h51gYtqs9d/YtuexI9kpuvMvK1x3zU+VtnmDiv9xFd7LxO6i3P/EAYmZ0PSFBnlbv3eLuFmXCuR84qUNla8r8CyItRtYowkraqCZWKl39tmO8Uisy7fOf+CCfYPEeQ8y5MyU9radkMGyhorj5Dfbut07d+hf4TAOWdOJurvoPG2OqLgFXAV83qvt1Jn+Pig+s1P4u1v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer " = _t, Keywords = _t, Catogery = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Customer ", Text.Trim, type text}, {"Keywords", Text.Trim, type text}, {"Catogery", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Customer ", type text}, {"Keywords", type text}, {"Catogery", type text}}),


    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "index", each Table.SelectRows(#"Hierarchy table", (r)=>Text.Contains(r[CategoryOrder],[Catogery]))[Index]{0}
),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Customer "}, #"Added Custom", {"Customer "}, "Added Custom", JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(#"Merged Queries", "MinIndex", each List.Min([Added Custom][index])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Highest Parent", each Table.SelectRows([Added Custom], (x)=>x[index]=[MinIndex])[Catogery]{0}
),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"index", "Added Custom", "MinIndex", "Keywords", "Catogery"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[BA_Pete]

Hi @chem094 ,

To create an overall category, you can use your keyword method, but just expand the list of keywords that apply, something like this:

if Text.Contains([Customer], "Acad")
or Text.Contains([Customer],"School")
or Text.Contains([Customer], "Edu")
then "Education"
else if Text.Contains([Customer], "ag")
or Text.Contains([Customer], "Company")
then "Company"
else if...
...
else //your escape value goes here, like null or "Error" or similar

Pete

[chem094]

Thanks Pete for your help..  my exampleis with 1 org and multiple keywords from it to map hierarchy table to provide single hierarchical value.. However, my data sample has 10k + orgs in which more than 3 keywords match found but one hierarchy value is to be given as result from hierarchy table.. 

[BA_Pete]

@chem094 ,

OK, I think I see what you mean.

I think you need to group your table on [Customer] and set the aggregation column as 'All Rows' and call it [data].

Then create a new custom column, something like this:

if List.Contains("Clarivate", [data][Category]) then "Clarivate"
else if List.Contains("Library", [data][Category]) then "Library"
else if List.Contains("Publication", [data][Category]) then "Publication"
...
//and so on, in rank order
...
else //your escape value

Then expand your [data] column to reinstate the original columns that you need.

Pete