Add Column based on VLOOKUP and CONCATENTATION

[Alicia83B]

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!

[JamesRobson · Accepted solution]

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}})

[Einomi]

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

[KT_Bsmart2gethe]

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]

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

[KT_Bsmart2gethe]

Hi @Alicia83B ,

Use GroupBy function:

Step1

Step2

Regards

KT