Replace values in a column if it contains a certain text

[FD5]

Hi everyone,

I hope that someone can help me solve my query as I am half way there I think!

I have merged two columns in a table but now I need to replace the values to make more sense of the descriptions. I don't want to go through the list in that column one by one as it is a long list but the description contains some key words that I could use to replace in bulk. Some examples below:

The list below, I want to replace them all with a word 'PFI':

Private Finance Initiative-Holding Company

Private Finance Initiative-Limited Company

Private Finance Initiative-Limited Partnership

Private Finance Initiative-Limited Liability Partnership

Private Finance Initiative-Partnership

I started working on a DAX but I am stuck. Here is my formula (my column header is Industry & Org Type):

= Table.ReplaceValue(#"Inserted Merged Column",
each [Industry & Org Type],
each if List.Contains(Private, [Industry & Org Type]) then "PFI" else [Industry & Org Type],
Replacer.ReplaceText,{"Industry & Org Type"})

Any idea where I am going wrong? Any help will be appreciated!

Many thanks

[AntrikshSharma · Accepted solution]

@FD5 There are multiple ways to do this:

Single replacement:

let
    Source = Table,
    ReplacedValue = Table.ReplaceValue (
        Source,
        each [Column1],
        each if Text.Contains ( [Column1], "Private" ) then "PFI" else [Column1],
        Replacer.ReplaceValue,
        { "Column1" }
    )
in
    ReplacedValue

Single Replacement with custom function:

let
    Source = Table,
    ReplacedValue = Table.ReplaceValue (
        Source,
        each [Column1],
        each "PFI",
        ( OriginalText, Check, ReplacementText) =>
            if Check = true
            then ReplacementText
            else OriginalText,
        { "Column1" }
    )
in
    ReplacedValue

Multiple Replacements:

let
    Source = Table,
    AddedCustom = Table.AddColumn (
        Source,
        "Replacements",
        ( x ) =>
            List.Select (
                Replacements,
                ( y ) => Text.Contains ( x[Column1], y{0}, Comparer.OrdinalIgnoreCase )
            ){0}?{1}?
    )
in
    AddedCustom

Multiple Relacements with Table.TransformRows 

let
    Source = Table.FromRecords (
        Table.TransformRows (
            Table,
            ( x ) =>  [
                Column1 = x[Column1],
                NewText = List.Select (
                    Replacements,
                    ( y ) => Text.Contains ( x[Column1], y{0}, Comparer.OrdinalIgnoreCase )
                ){0}?{1}?
            ]
        )
    )
in
    Source

Refer to the file attached below:

[FD5]

Many thanks. I will have a go at this solution.