Forum Discussion

chem094's avatar
chem094
Frequent Visitor
4 years ago
Solved

Group by duplicate column to lookup Hierarchy Table for multiple values

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
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      chem094
      Frequent Visitor
      4 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        4 years ago

        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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.