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