Replace Value in Text String if Match

Question

Hello,&nbsp;I have a question regarding dax syntax in power query using data similiar to the sample data found below:&nbsp;Input Color&nbsp;Input Values&nbsp;Outputblue&nbsp;ten, blue, vegetable, milkshake&nbsp;ten, vegetable, milkshakegreen&nbsp;green, onion, twelve, tea&nbsp;onion, twelve, teared&nbsp;carrots, water, thirteen, train&nbsp;carrots, water, thirteen, trainyellow&nbsp;tree, bird, four, yellow&nbsp;tree, bird, four&nbsp;Essentially, I'm trying to create a new column similiar to the Output column. The functionality of the dax query should involve checking if the Input Color is present in the Input Values column, and if so, removing the Input Color from the list of values. The Output column reflects how the data values should end up looking after the process is complete.&nbsp;Note: the Input Color value can be located at the beginning, in the middle, or at the end of the Input Values string. I've tried using the Replace.Values functionality, but I'm struggling to keep the other values in the list included in the Output column when there is a match to the Input Color.&nbsp;Any help is greatly appreciated.&nbsp;Thank you,Paul

v-yueyunzh-msft · Accepted Answer

HI ,&nbsp;Anonymous&nbsp;
According to your description, you want to&nbsp;Replace Value in Text String if Match.Thanks for your sample data and your sample data the splite text is ", ".
Here are the steps you can refer to :(1)My test data is the same as yours.
(2)You can create a blank query in Power Query Editor and create a "Advanced Editor":

&nbsp;
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY3BCsMwDEN/JeTsPwo5uKvWhnoxuG7D/n5u2EkCPUml5EUuZMqOTunxlG5scF4k7KfJce58IFcqeTOgBzuVkvamIT4gd7AOnpRhDebFZuonpcEOi3Rv5rPmxq1P8gsRHc95DMZ7s5XSW6/A/1GtPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Input Color" = _t, #"Input Values" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input Color", type text}, {"Input Values", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{"Input Values",(x)=&gt;  Text.Split(x,", ")   }),
    #"Added Custom" = Table.AddColumn(Custom1, "Output", (x)=&gt;   List.Difference( x[Input Values] ,{x[Input Color]}    )     ),
    #"Removed Columns" = Table.TransformColumns(Table.RemoveColumns(#"Added Custom",{"Input Values"}), {"Output",(x)=&gt;Text.Combine(x,", ")     }  )
in
    #"Removed Columns"
&nbsp;
Then wen can get the result as follows:

&nbsp;
&nbsp;
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!&nbsp;
&nbsp;
Best Regards,
Aniya Zhang
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly
&nbsp;

Forum Discussion

Replace Value in Text String if Match

2 Replies

Related content

Replacing string values in a query column with values from another query

Replacing values with Iterations

Replace string values with wildcard

Replace Errors values with Null values

Replace any Value/Text/String by number

Input Color	Input Values	Output
blue	ten, blue, vegetable, milkshake	ten, vegetable, milkshake
green	green, onion, twelve, tea	onion, twelve, tea
red	carrots, water, thirteen, train	carrots, water, thirteen, train
yellow	tree, bird, four, yellow	tree, bird, four