Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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
@AlB it did not change the tag - it remained the same. Is the problem that the ".CV" is in between other characters?
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |