Power Query | Batch Replace String

[ovetteabejuela]

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. 😃

[ovetteabejuela]

Okay.

Found @ImkeF's post, but I'm still trying to wrap my head around it.

[Anonymous]

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,

[Anonymous]

Hi @ovetteabejuela.

I think you are complicating some easy to do with just using a merge query.

Bye.

Miltinho

[ovetteabejuela]

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.

[ovetteabejuela]

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...

[MarcelBeug]

A different approach.

Assuming:

1. You want to replace values in an entire table, and
2. 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)).

[ovetteabejuela]

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.

@ImkeF,

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.

@MarcelBeug,

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.

[ovetteabejuela]

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

[MarcelBeug · Accepted solution]

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.

[ovetteabejuela]

As usual from you @MarcelBeug an excellent knowledge share, Another gem in my M treasure chest..

Thank you!!!

[ImkeF]

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
Magic