Basic syntax

[k1s]

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

 

[Chihiro]

Upload sample of your raw data along with your query within same workbook. Also show how should resulting table look like (created manually if needed).

 

[k1s]

Hello again Chihiro, I'm not in a position to do that right now.

This line in the query works for finding and removing the word "Band" from any column headings the "Band" appears in:

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

I want to alter the line to find and remove "Band" and/or another string, e.g. "Next Word"

I assumed it is something to do with curly brackets around pairs of the string to find and the string to replace, but I can't find any documented reference that explains the syntax.

I've tried:
{"1st_find", "1st_replacement"},{"2nd_find"}, "2nd_replacement"}
{{"1st_find", "1st_replacement"},{"2nd_find"}, "2nd_replacement"}},
{"1st_find", "1st_replacement", "2nd_find", "2nd_replacement"},
but none of these curly bracket permutations has worked for me.

I'm hoping somebody can tell me the correct syntax for finding and replacing several strings instead of just one.

 

[r2c2]

@k1s I haven't got PQ on this comp (I will test later when I get home). But I think you can use this syntax.

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

Just add as many nested Text.Replaces as you want. You may be able to use a custom function to take input mapping range (what to find and what to replace in a 2 column range / table) and apply Text.Replace iteratively. I am not sure how to do it as I have never done it.

All the best.

 

[k1s]

Thanks, that works for me.