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

[Anonymous]

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	johnsmith@company.com
1235	company 2	JANE	Smith	janesmith@company.com
1236	company 3	Joan	Smith	joansmith@company.com
1236	company 1	JOHN	Smith	johnsmith@company.com

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

Vendor_ID	Vendor_Name	Contact_FN	Contact_LN	Email	Concatenate
1234	company 1	JOHN	Smith	johnsmith@company.com	1234, 1236
1235	company 2	JANE	Smith	janesmith@company.com	1235
1236	company 3	Joan	Smith	joansmith@company.com	1236
1236	company 1	JOHN	Smith	johnsmith@company.com	1234, 1236

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

[smpa01 · Accepted solution]

@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]

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]

@Anonymous  change vendor_name with Email in the code

[JM_BEI]

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]

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(_)
        ), 
        " / "
    ))