Group by duplicate column to lookup Hierarchy Table for multiple values

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.