Showing posts with label powerquery. Show all posts
Showing posts with label powerquery. Show all posts

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