Forum Discussion

Alicia83B's avatar
Alicia83B
Helper I
4 years ago
Solved

Add Column based on VLOOKUP and CONCATENTATION

I am looking to add a new column based on looking up an opportunity number and concatentating the products from another column. A comma would be used to separate each Product Code. 

 

Here is an example of what my data looks like.  What formula would I use to complete what I am looking to do? Also, I am open to another way of getting this done if VLOOPUP and CONCATENTATION wouldn't be the best way to get what I am looking for. 


Opportunity #      Product Code

195314                 727

195313                 728

195314                 721

195313                 720

195314                 840

195313                 730

201331                 8240

 

Thank you in advance for your help!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Alicia,

     

     Think this might give you the desired result (just check the column names first):

     

    = Table.Group(#"Renamed Columns", {"Opportunity"}, {{"Count", each Table.RowCount(_), type number},{"ProductCode", each Text.Combine([ProductCode],","), type text}})

     

5 Replies

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    HI Alicia83B ,

     

    I am not sure if I am100% understand your desired outcome. Would you mind providing a view of your desired outcome?

     

    Regards

    KT

    • Alicia83B's avatar
      Alicia83B
      Helper I
      4 years ago

      Hi KT_Bsmart2gethe 

       

      Below is my desired outcome. Instead of having an Opportunity # listed multiple times due to multiple Product Codes, I would be able to show the Opportunity # with all the Product Codes in one cell in the next column. 

       

      Opportunity #    Product Code

      195314                727, 721, 840

  • Einomi's avatar
    Einomi
    Helper V
    4 years ago

    Hi,

    Please try the following M code

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Opportunity #", Int64.Type}, {"Product Code", type text}}),
    Sort = Table.Group(#"Type modifié", {"Opportunity #"}, { {"New", each Text.Combine([Product Code],", "), type number}})
in
    Sort

     

    Please not your column Product Code needs to be formatted as text before applyting this code

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Alicia,

     

     Think this might give you the desired result (just check the column names first):

     

    = Table.Group(#"Renamed Columns", {"Opportunity"}, {{"Count", each Table.RowCount(_), type number},{"ProductCode", each Text.Combine([ProductCode],","), type text}})