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_IDVendor_NameContact_FNContact_LNEmail
1234company 1JOHN                                    Smith                                                       [email protected]
1235company 2JANE                                    Smith                                                       [email protected]
1236company 3JoanSmith                                                       [email protected]
1236company 1JOHN                                    Smith                                                       [email protected]

 

My objective is to find contacts who have more than one Vendor_ID, and then, concatenate them into one cell like so:

 

 

Vendor_IDVendor_NameContact_FNContact_LNEmailConcatenate
1234company 1JOHN                                    Smith                                                       [email protected]   1234, 1236
1235company 2JANE                                    Smith                                                       [email protected]   1235
1236company 3JoanSmith                                                       [email protected]   1236
1236company 1JOHN                                    Smith                                                       [email protected]  1234, 1236

 

How can I achieve this using M, or Power Query only? 

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"

5 Replies

  • smpa01's avatar
    smpa01
    Community 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
        Community Champion
        4 years ago

        Anonymous  change vendor_name with Email in the code