If Text.Contains(string, {substringsA} as list) insert {substringsB} else insert original string

[eliPR]

I have a table Projects with a column called [Company Name].

I have another table #"Replacement Names" with two columns, [If Company Name Exists] and [New Company Name] (see below)

I want to create a new column in Projects table, and, for each record in [Company Name], I want power query to loop through [If Company Name Contains]. If the record contains that substring, I want power query to insert the string from #"Replacement Names"[New Company Name] in the new column. If it does not contain any of the strings in [If Company Name Contains], I want it to insert the string from [Company Name].

I created a conditional column to do this (M code below), and it works, but I think it would be more manageable to do it the way I've outlined above.

Any help is appreciated.

Thanks!

M code for Conditional Column⬇

[Anonymous]

Hi @eliPR ,

It is more convenient to use the Merge operation to complete.

My sample data.

1.Do the Merge operation in the Projects table.

2.Expand the [New Company Name] column.

3.Add a conditional column named Replaced Name.

4.This is the results.

You can download the attachment to view the details.

Best Regards,

Stephen Tao

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

[AlexisOlson]

A merge works well if you have an exact match but I think @eliPR was only expecting your [If Company Name Exists] to be a substring of [Company Name] rather than a column you can join on.

[AlexisOlson]

Try this as a custom column:

(row) =>
List.Max(
    Table.SelectRows(
         #"Replacement Names",
         each Text.Contains(row[Contractor Clustered], [If Company Name Contains])
    )[New Company Name]
)