Recursive functions

Overview

The @ (at) sign in Power Query is used for recursion. It is a programming method within the M language that allows a function to call itself when needed.

A useful practical use of recursive functions is when you might need to, for example, replace several text strings from a table, all at one.

Currently, the only way of replacing a text string in Power Query with something else, is through the Power Query, Replace Values feature. But this can only find and replace one value at a time.

With this restriction, if you had to make 15 replacements, you would have to click on the ‘Replace Values’ icon 15 times, each time going through the configuration menu.

With recursive functions, these replacement operations can all be done in 1 step, and as a consequence, save you a tremendous amount of time. This article explains how to use recursive functions, to achieve just this.

The Scenario

Let’s suppose you have two data tables:

One that contains the sentences with text strings you want to replace in the 2nd column. The table is called “Text_Table”

The second, that holds value pairs – with Old text, and then New text that replaces the Old text. The table is called “Translation_Table”

Steps

1. Load the Tables to Power Query

  • To get started, load both tables into Power Query
  • Click on any cell in each table ➔ click on the From Table / Range icon under the Data tab
  • Give both Queries the same names as their Table names in Excel

2. Create a new Query

Now, create a new, third query.

  • Click on the Get Data icon in Excel ➔ From Other Sources ➔ Blank Query

Start the query with the following code:

M-Code 
    let
        Source = Text_Table,
        Old = List.Buffer(Translation_Table[OldText]),
        New = List.Buffer(Translation_Table[NewText])
    in
        New

Here we load the Query called Text_Table, and then buffer the two separate columns of the Translation_Table. The buffering is needed for performance reasons, to make sure the data in the Translation_Table is quickly accessible.

3. Create a recursive function

  • Next, create a new step to the above query, and call it fxTranslate.
  • Code it as follows:
M-Code 
fxTranslate = (x as table, n as number, ColName as text ) as table =>
let
    Replace = Table.ReplaceValue(x, Old{n}, New{n}, Replacer.ReplaceText,{ColName}),
    Checking = if n = List.Count(Old)-1 then Replace else @fxTranslate(Replace, n+1, ColName )
in
    Checking

The parameters of this function are:

  • x – this is the input table for the function
  • n – this is a counter
  • ColName – this is where we input the name of the column inside the ‘x’ table, whose text strings we want to replace

The two steps of the function are:

  • Replace – this is where the Table.ReplaceValue function is used. This references to the Text_Table, the Old and New lists created earlier, and the name of the column of the Text_Table that needs to be used for the replacement.
  • Checking – this is where we decided whether to use recursion or not, based on the counter. The counter starts from 0, and on each iteration is compared to the total number of elements inside the Old list. The recursion will keep going, until the counter equals the number of elements in the Old list. The @fxTranslate(Replace, n+1, ColName) code, is where the recursion gets defined.

The code should now look like this:

M-Code 
let
    Source = Text_Table,
    Old = List.Buffer(Translation_Table[Old Text]),
    New = List.Buffer(Translation_Table[New Text]),
    fxTranslate = (x as table, n as number, ColName as text ) as table =>
        let
            Replace = Table.ReplaceValue(x, Old{n}, New{n}, Replacer.ReplaceText,{ColName}),
            Checking = if n = List.Count(Old)-1 then Replace else @fxTranslate(Replace, n+1, ColName )
        in
            Checking
in
    fxTranslate

4. Invoke the function

The function can now be invoked, by inserting one more line of code, as shown below:

M-Code 
let
    Source = Text_Table,
    Old = List.Buffer(Translation_Table[Old Text]),
    New = List.Buffer(Translation_Table[New Text]),
    fxTranslate = (x as table, n as number, ColName as text ) as table =>
        let
            Replace = Table.ReplaceValue(x, Old{n}, New{n}, Replacer.ReplaceText,{ColName}),
            Checking = if n = List.Count(Old)-1 then Replace else @fxTranslate(Replace, n+1, ColName )
        in
        Checking,
    Translation = fxTranslate (Source, 0, "Text Content")
in
    Translation

The query should now successfully load as follows, with all the necessary text replacements made!

(You can also add more items to the Translation_Table, and refresh the query again, to instantly update for any new replacements!)