Find and replace part of word in the first row

[aki2lan]

Hi Everyone, 

In Power Query, is it possible to find and replace or substitute part of a word? 

So for instance in the table below, before I make the first row my header - I would like to transform all the tags from Alpha1/AI.CV (m3/h) to Alpha1/AI (m3/h) etc. (i.e. remove the .CV from all first row). 

So in excel you could do, find= ".CV (" and replace with = " ("

(notice that there is a space in front of bracket)

Currently:

Time (s)	Alpha1/AI.CV (m3/h)	Beta2/BI.CV (%)	Charlie3/CI.CV (%)
04/06/2020 13:35:00	136	45	99
04/06/2020 13:35:01	130	45	95
04/06/2020 13:35:02	150	45	105

Preferred Outcome: 

Time (s)	Alpha1/AI (m3/h)	Beta2/BI (%)	Charlie3/CI (%)
04/06/2020 13:35:00	136	45	99
04/06/2020 13:35:01	130	45	95
04/06/2020 13:35:02	150	45	105

[AlB]

Hi @aki2lan 

You can just select all the column where you want that change, click on the right mouse button, select "Replace values" and there

Value to Find

".CV ("

Replace with -->

" ("

After that you can make the firt row into the column names

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[aki2lan]

@AlB it did not change the tag - it remained the same. Is the problem that the ".CV" is in between other characters? 

[AlB]

@aki2lan 

Write the exact values you need found and replaced, without the quote-unquote characters, i.e.

Value to Find

.CV (

Replace with -->

 (

Otherwise it will search for ".CV (" instead of  .CV (

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[aki2lan]

@AlB Yes I tried it both ways. Once I did the replace, I refreshed preview and it still didn't change the first row

[aki2lan]

I just tested the find and replace using the entire tag: 

Find: Alpha1/AI.CV (m3/h)

Replace with: Alpha1

That works! So to me it looks like power query cannot find specific characters inside a word, i.e. ".CV ("

Any solution for this? 

[aki2lan · Accepted solution]

It turns out the first row needed to be transposed (so first row becomes first column), convert the first column with tags to text only and then do a find/replace on first column.