Match values in comma seperated column

[Anonymous]

Hello,
 
I have a question regarding comma seperated values.
 
The example data consists out of three columns: "Product_ID" (unique identifier), "Parts_Used" (comma sperated list) and "Price" (simple number).
 
As you can see in the "Matching"-table I'd like to be able to change the part names by updating the corresponding names within this table. The problem is that I want to keep the comma seperated form of the "Parts_Used" column.
 
Is this somehow possible?

Here you can find the sample file:

LINK 

Kind regards

Nilso

[dax]

Hi @Anonymous , 

It seems that you upload wrong sample, I didn't see "Product_ID" (unique identifier), "Parts_Used" (comma sperated list) and "Price" (simple number) in your link. So if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I'm sorry, this was my mistake!

LINK 

Thank you 🙂

[dax · Accepted solution]

Hi @Anonymous , 

You could try to use M code to achieve this goal. You could refer to my sample for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9LCoAwDAWvIll30XzVW7gvRbyCeH+MrSmIu0dnmpeUAggJtuO89oxp6oEjmDMkFqipAA2RgmuExZl72kT+TcT4gU8bslATZYhrDJr9SYU718FHkX0b39UsRMpRFDfg2m5Qg1pv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Parts_Used = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product_ID", Int64.Type}, {"Parts_Used", type text}, {"Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Parts_Used], ",")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom"," ","",Replacer.ReplaceText,{"Custom"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Custom"}, #"Table (2)", {"Part_Old"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Part_New"}, {"Part_New"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table (2)",{"Parts_Used", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Product_ID"}, {{"Part_new", each Text.Combine([Part_New], ","), type text}, {"Price", each List.Average([Price]), type number}})
in
    #"Grouped Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.