Replace Value in Text String if Match

[Anonymous]

Hello,

I have a question regarding dax syntax in power query using data similiar to the sample data found below:

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

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.

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.

Any help is greatly appreciated.

Thank you,

Paul

[v-yueyunzh-msft · Accepted solution]

HI , @Anonymous 

According to your description, you want to 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":

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)=>  Text.Split(x,", ")   }),
    #"Added Custom" = Table.AddColumn(Custom1, "Output", (x)=>   List.Difference( x[Input Values] ,{x[Input Color]}    )     ),
    #"Removed Columns" = Table.TransformColumns(Table.RemoveColumns(#"Added Custom",{"Input Values"}), {"Output",(x)=>Text.Combine(x,", ")     }  )
in
    #"Removed Columns"

Then wen can get the result as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Anonymous]

Amazing! This is exactly what I needed. Thank you so much.