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.
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 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...
Having difficulties to follow, but maybe this does help?:
let
TranslationTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSs1Mz1PwScxLL01MT1UwVNJR8sxTcM1Lz8kszgByY3WwqDJCVWWEXZUxqipj7KpMUFWZYFdliqrKFLsqM1RVZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Foreign Language" = _t, #"In English" = _t]),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSs1Mz1PwScxLL01MT1UwVNLBFDTCJmiMTdAEm6ApNkEzpVidAbY/FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
fnTranslate = (Record) =>
let
ToTable = Record.ToTable(Record),
#"Merged Queries1" = Table.NestedJoin(ToTable,{"Value"},TranslationTable,{"Foreign Language"},"Ex",JoinKind.LeftOuter),
#"Expanded Ex" = Table.ExpandTableColumn(#"Merged Queries1", "Ex", {"In English"}, {"In English"})[In English]
in
#"Expanded Ex",
Apply = Table.AddColumn(Source, "a", each fnTranslate(_)),
Magic = Table.FromRows(Apply[a], Table.ColumnNames(Source))
in
MagicA 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.
