Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago
Solved

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 ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi ovetteabejuela.

     

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

    Bye.

     

    Miltinho

  • ovetteabejuela's avatar
    ovetteabejuela
    Impactful Individual
    9 years ago

    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's avatar
      ovetteabejuela
      Impactful Individual
      9 years ago

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

      • ImkeF's avatar
        ImkeF
        Community Champion
        9 years ago

        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