cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Find and replace part of word in the first row

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:001364599
04/06/2020 13:35:011304595
04/06/2020 13:35:0215045105

 

Preferred Outcome: 

Time (s)Alpha1/AI (m3/h)Beta2/BI (%)Charlie3/CI (%)
04/06/2020 13:35:001364599
04/06/2020 13:35:011304595
04/06/2020 13:35:0215045105
1 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.

View solution in original post

6 REPLIES 6
Super User III
Super User III

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 

SU18_powerbi_badge

Regular Visitor

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

@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 

SU18_powerbi_badge

 

Regular Visitor

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

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? 

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.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors