Thursday, February 6, 2020

Powerquery multi replace

Function to replace multiple text strings based on a table with "Old" and "New" Columns. For powerquery / m
let
    multiReplace= (InputText as text,tOldNew as table)as text=> 
     let
       //Use List.Generate() to do the replacements
       DoReplacement = List.Generate(
                          ()=> [Counter=0, MyText=InputText], 
                          each [Counter]<Table.RowCount(tOldNew), 
                          each [Counter=[Counter]+1, 
                                MyText=Text.Replace(
                                         [MyText], 
                                         tOldNew[Old]{Counter}, 
                                         tOldNew[New]{Counter}
                                        )
                                ], 
                          each [MyText]),
       //Return the last item in the list that
       //List.Generate() returns
       GetLastValue = List.Last(DoReplacement)
     in
      GetLastValue
in
    multiReplace

No comments:

Post a Comment