Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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}})
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}})
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
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
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