Syntax to replace multiple text strings

[kcmuppet]

<article>

Hi. I'm new to power query, so please be gentle!

I've got a simple query to remove text from all column names:

#"Transform Column Names" = Table.TransformColumnNames(#"Reordered Columns1",(columnName as text) as text => Text.Clean(Text.Replace(columnName, "Band", "")))

How can I alter the query to remove multiple different words (not necessary adjacent).

(I tried:

#"Transform Column Names" = Table.TransformColumnNames(#"Reordered Columns1",(columnName as text) as text => Text.Clean(Text.Replace(columnName, {{"Band", ""},{"Next Word", ""}})))

...but that didn't work)

Can't make any sense of the MS reference page:
https://msdn.microsoft.com/en-us/library/mt674878.aspx?f=255&MSPPError=-2147217396​

</article>

 

[FranzV]

You can use the following code to do multiple replacements if the words are separated by a space.

MultiReplacementForHeaders = 
    Table.TransformColumnNames( 
        YourTable, 
        each 
            Text.Combine(
                List.Difference( 
                    Text.Split( _, " " ), 
                    { "Word1", "Word2", "Word3" } 
                ),
                " "
            )
    ) as table

• Text.Split() splits the ColumnName at every space into a list.
• List.Difference() removes "Word1", "Word2", and "Word3" from the list of words.
  • You can change the words to change and add as many as you like.
• Text.Combine() merges the list back into a single string.

I think I learned this from Imke Feldmann, but it might have been somewhere else. I hope it helps.

 

[kcmuppet]

Hi Thanks,

That works

Somebody on Chandoo's forum suggested nesting like this, which also works:

[COLOR=#141414][FONT=Verdana]#"Transform Column Names" = Table.TransformColumnNames(#"Reordered Columns1",(columnName as text) as text => Text.Clean(Text.Replace(Text.Replace(columnName, "Band", ""),"New Word","")))[/FONT][/COLOR]

 

[FranzV]

Thtat should work, but it might be harder to maintain if you want to add or delete replacements.

I actually added the MultiRemove function to my custom function library so I can get the same result with

MultiReplacementForHeaders = 
    Table.TransformColumnNames( 
        YourTable, 
        each fnMultiRemove( _, {"Word1", "Word2", "Word3"} )
    ) as table

The code for the function is:

//fnMultiRemove
(
    String as text, 
    WordsToRemove as list,
    optional OriginalDelimiter as nullable text, 
    optional NewDelimiter as nullable text
) as text =>


let
    oDelimiter = 
        if OriginalDelimiter is null then " " else OriginalDelimiter as text,
    nDelimiter = 
        if NewDelimiter is null then oDelimiter else NewDelimiter as text,
    Split = 
        Text.Split( String, oDelimiter ) as list,
    Remove = 
        List.Difference( Split, WordsToRemove ) as list,
    Combine = 
        Text.Combine( Remove, nDelimiter ) as text
in
    Combine


/*
Removes the specified words from a string and optionally changes the word delimiter.
*/