Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Concatenate duplicate values into one cell for a given ID using Power Query?

I have an excel table imported into Power Query shown below.    Vendor_ID Vendor_Name Contact_FN Contact_LN Email 1234 company 1 JOHN                                     Smith         ...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous 

     

     

    Text.Combine(
      List.Transform(
        List.Distinct(
          Table.SelectRows(#"Changed Type", (q) => q[Vendor_Name] = [Vendor_Name])[Vendor_ID]
        ), 
        each Number.ToText(_)
      ), 
      ","
    )

     

     

    if you don't want distinct

    Text.Combine(
      List.Transform(
        /*List.Distinct(*/
          Table.SelectRows(#"Changed Type", (q) => q[Vendor_Name] = [Vendor_Name])[Vendor_ID]
       /* )*/, 
        each Number.ToText(_)
      ), 
      ","
    )

     

    let
  Source = Web.BrowserContents(
    "https://community.powerbi.com/t5/Power-Query/Concatenate-duplicate-values-into-one-cell-for-a-given-ID-using/m-p/2277750#M67528"
  ), 
  #"Extracted Table From Html" = Html.Table(
    Source, 
    {
      {"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"}, 
      {"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"}, 
      {"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"}, 
      {"Column4", "TABLE:nth-child(3) > * > TR > :nth-child(4)"}, 
      {"Column5", "TABLE:nth-child(3) > * > TR > :nth-child(5)"}
    }, 
    [RowSelector = "TABLE:nth-child(3) > * > TR"]
  ), 
  #"Promoted Headers" = Table.PromoteHeaders(
    #"Extracted Table From Html", 
    [PromoteAllScalars = true]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {
      {"Vendor_ID", Int64.Type}, 
      {"Vendor_Name", type text}, 
      {"Contact_FN", type text}, 
      {"Contact_LN", type text}, 
      {"Email", type text}
    }
  ), 
  #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(
      List.Transform(
        /*List.Distinct(*/
          Table.SelectRows(#"Changed Type", (q) => q[Vendor_Name] = [Vendor_Name])[Vendor_ID]
       /* )*/, 
        each Number.ToText(_)
      ), 
      ","
    ))
in
  #"Added Custom"
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous 

 

 

Text.Combine(
      List.Transform(
        List.Distinct(
          Table.SelectRows(#"Changed Type", (q) => q[Vendor_Name] = [Vendor_Name])[Vendor_ID]
        ), 
        each Number.ToText(_)
      ), 
      ","
    )

 

 

if you don't want distinct

Text.Combine(
      List.Transform(
        /*List.Distinct(*/
          Table.SelectRows(#"Changed Type", (q) => q[Vendor_Name] = [Vendor_Name])[Vendor_ID]
       /* )*/, 
        each Number.ToText(_)
      ), 
      ","
    )

 

let
  Source = Web.BrowserContents(
    "https://community.powerbi.com/t5/Power-Query/Concatenate-duplicate-values-into-one-cell-for-a-given-ID-using/m-p/2277750#M67528"
  ), 
  #"Extracted Table From Html" = Html.Table(
    Source, 
    {
      {"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"}, 
      {"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"}, 
      {"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"}, 
      {"Column4", "TABLE:nth-child(3) > * > TR > :nth-child(4)"}, 
      {"Column5", "TABLE:nth-child(3) > * > TR > :nth-child(5)"}
    }, 
    [RowSelector = "TABLE:nth-child(3) > * > TR"]
  ), 
  #"Promoted Headers" = Table.PromoteHeaders(
    #"Extracted Table From Html", 
    [PromoteAllScalars = true]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {
      {"Vendor_ID", Int64.Type}, 
      {"Vendor_Name", type text}, 
      {"Contact_FN", type text}, 
      {"Contact_LN", type text}, 
      {"Email", type text}
    }
  ), 
  #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(
      List.Transform(
        /*List.Distinct(*/
          Table.SelectRows(#"Changed Type", (q) => q[Vendor_Name] = [Vendor_Name])[Vendor_ID]
       /* )*/, 
        each Number.ToText(_)
      ), 
      ","
    ))
in
  #"Added Custom"
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Is there a way to use Email instead of the vendor name? The email addresses are unique, the vendor names unfortunately are not and have tons of repeat values.

 

Also, if there is an email address that has duplicate in two rows, with different vendor ID, what will the effect be? 

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous  change vendor_name with Email in the code

    • JM_BEI's avatar
      JM_BEI
      Frequent Visitor
      3 years ago

      Hi, I tried adapting this solution to my own problem but got the folowing error:

       

      Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

       

      is there something I'm missing?

       

       

          #"Concatenate" = Text.Combine(
      List.Transform(
        List.Distinct(
          Table.SelectRows(#"Removed Columns", (q) => q[RepeatedValue] = [RepeatedValue])[TextToCombine]
        ), 
        each Number.ToText(_)
      ), 
      " / "
    )

       

       

      • JM_BEI's avatar
        JM_BEI
        Frequent Visitor
        3 years ago

        Got the solution.

         

        I was missing the Table.AddColumn code.

            #"Concatenate" = Table.AddColumn(#"Filtered Rows1", "New Column", each Text.Combine(
      List.Transform(
        List.Distinct(
          Table.SelectRows(#"Filtered Rows1", (q) => q[RepeatedValue] = [RepeatedValue])[TextToCombine]), 
        each Number.ToText(_)
        ), 
        " / "
    ))