Forum Discussion
Power Query | Batch Replace String
Hi,
How do I replace this:
| Foreign Language 1 | Foreign Language 2 | Foreign Language 3 | Foreign Language 4 | Foreign Language 5 | Foreign Language 6 |
into this in a single pass:
| In English 1 | In English 2 | In English 3 | In English 4 | In English 5 | In English 6 |
I can work with a table with an equivalent word in English for each foreign word but I'm just not sure how to do that in M.
[Edit]
Hi MarcelBeug,
Sorry to drag you into this but I think this is a piece of cake for you. =)
In that case you can use this code:
= Table.TransformColumnNames(Table1, each try Translations{[Foreign = _]}[English] otherwise _)It searches each column name in the Translations table and - if found - replaces it with English; if not found then leave it as is.
11 Replies
Hi ovetteabejuela..
If the diferent texts are in a row first you have to select all these columns and unpivot using Unpivot Columns from Transform tab. In In this way you have only a column with the diferent texts.
Then, You just select the column and to use "1-->2 Replace Values" from Transform tab.
Tell us if this help you.
Miltinho,
Hi ovetteabejuela.
I think you are complicating some easy to do with just using a merge query.
Bye.
Miltinho
Hi Anonymous,
Thank you for your input.
Yes I was aware of the Replace Value method, however I was looking for something that works in a batch. So that if there are 10 words to replace I don't have to do 10 lines of Replace Values function.
ok still!
just about to apply the solution but I'm already at a halt because of this:
Mine:
let Source = Excel.Workbook(#"Sample File Parameter1", null, true),
ImkeF's(in the comment section of the post)
let Source = ReplacementsTable,
ImKef started on the ReplacementsTable while I'm starting at my Raw data... hmmmm.... thinking... but could really use some help...
