Forum Discussion
Power Query | Batch Replace String
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.
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...
A different approach.
Assuming:
- You want to replace values in an entire table, and
- You want to replace complete values (not substrings, but entire cell contents).
Then you can use List.Accumulate to loop (or iterate) over the list of translations:
List.Accumulate(Table.ToRows(Translations),Table1,(t,r) => Table.ReplaceValue(t,r{0},r{1},Replacer.ReplaceValue,Table.ColumnNames(t)))
Explanation:
- Translations is a table with translations (foreign - English), which is converted to a list of lists with replacements using Table.ToRows
- Table1 is your table. For List.Accumulate this table is the start value.
- The third argument for List.Accumulate is a function using a "state" and "current" parameter (i.c. t and r)
t is the table before each iteration and r is the foreign - English pair of the current iteration. - List.Accumulate will loop over the translation list and for each entry (or iteration), it will update your table (t) by replacing the old value (r{0}) ny the new value (r{1}), using function Replacer.ReplaceValue, and for all table columns (Table.ColumnNames(t)).
Interesting inputs, first off apologies for not doing a great job in presenting my case - I forgot to highlight that in this particular data I only need to translate the Foreign Language found in the headers (though there are some entries in the data itself, but I don't need to translate them)
Anonymous, that is a neat approach I actually did that as well:
1. I demoted the header into the first row,
2. I merged the raw data and the translation table
3. Yes, indeed I got the translations but now the problem is -- how do I gt that over the first row so that if I transpose back I can promote the english version to the headers.
Your's is very interesting, I will perform that as well and see what I can learn from it. Though at my level I can't read the code the same way as reading a sentence, I can see there there is an add column function which make me think that it might not work because again I failed to indicate that I am working on the headers and that was my bad.
I think this one could work for me, but what do I do if I specify a column only, say Column1 since my first steps was to expose the headers and transpose it ready to be replaced/translated.
Thank you'all for the input, very much appreciated.
Basically, this is how the raw data looks like:
Foreign Language Header 1 Foreign Language Header 2 Foreign Language Header 3 Foreign Language Header 4 Foreign Language Header 5 Foreign Language Header 6 Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data 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.