Fanoloana lahatsoratra marobe amin'ny Power Query miaraka amin'ny fiasa List.Accumulate

How to quickly and in bulk replace the text according to the reference list with formulas – we have already sorted it out. Now let’s try to do it in Power Query.

As often happens manao this task is much easier than explaining Nahoana it works, but let’s try to do both 🙂

So, we have two “smart” dynamic tables created from ordinary ranges with a keyboard shortcut Ctrl+T na ekipa Trano - Atao ho latabatra (Horonan-trano - endrika toy ny latabatra):

I called the first table Data, the second table – Directorymampiasa saha Anaran'ny latabatra (Table name) vakizoro Mpanao (Volavola).

Task: replace in addresses in the table Data all occurrences from a column Mahita boky to their corresponding correct counterparts from the column mpisolo toerana. The rest of the text in the cells should remain untouched.

Step 1. Load the directory into Power Query and turn it into a list

Having set the active cell to any place in the reference table, click on the tab Data (Daty)na eo amin'ny tabilao Fanontaniana momba ny herinaratra (if you have an old version of Excel and you installed Power Query as an add-in on a separate tab) on the button Avy amin'ny latabatra/sehatra (Avy amin'ny latabatra/sahatra).

The reference table will be loaded into the Power Query query editor:

In order not to interfere, an automatically added step karazana novaina (karazana niova) in the right panel, the applied steps can be safely deleted, leaving only the step Source (Source):

Now, to perform further transformations and replacements, we need to turn this table into a list (list).

Ny digression tononkira

• table is a two-dimensional array consisting of several rows and columns.
• Record (Record) – one-dimensional array-string, consisting of several fields-elements with names, for example [Name = “Masha”, Gender = “f”, Age = 25]
  
• List – a one-dimensional array-column, consisting of several elements, for example {1, 2, 3, 10, 42} or { “Faith Hope Love” }
  

To solve our problem, we will be primarily interested in the type List.

The trick here is that list items in Power Query can be not only banal numbers or text, but also other lists or records. It is in such a tricky list (list), consisting of records (records) that we need to turn our directory. In Power Query syntactic notation (entries in square brackets, lists in curly brackets) this would look like:

{

    [ Find = “St. Petersburg”, Replace = “St. Petersburg” ] ,

    [ Find = “St. Petersburg”, Replace = “St. Petersburg” ] ,

    [ Find = “Peter”, Replace = “St. Petersburg” ] ,

etc.

}

Such a transformation is performed using a special function of the M language built into Power Query – Table.ToRecords. To apply it directly in the formula bar, add this function to the step code there Source.

Izy izay dia:

Rehefa avy:

After adding the Table.ToRecords function, the appearance of our table will change – it will turn into a list of records. The contents of individual records can be seen at the bottom of the view pane by clicking in the cell background next to any word Record (but not in a single word!)

In addition to the above, it makes sense to add one more stroke – to cache (buffer) our created list. This will force Power Query to load our lookup list once into memory and not recalculate it again when we later access it to replace it. To do this, wrap our formula in another function – List.Buffer:

Such caching will give a very noticeable increase in speed (by several times!) with a large amount of initial data to be cleared.

This completes the preparation of the handbook.

Dia mijanona ny tsindrio eo Home – Close and Load – Close and Load to… (Ao an-trano — Akatona&Ampidiro — Akatona&Ampidiro any..), mifidiana safidy Mamorona fifandraisana fotsiny (Mamorona fifandraisana ihany) and return to Excel.

Step 2. Loading the data table

Everything is trite here. As before with the reference book, we get up to any place in the table, click on the tab Data bokotra Avy amin'ny Table/Range and our table Data gets into Power Query. Automatically added step karazana novaina (karazana niova) you can also remove:

No special preparatory actions are required to be done with it, and we move on to the most important thing.

Step 3. Perform replacements using the List.Accumulate function

Let’s add a calculated column to our data table using the command Manampy Tsanganana – Tsanganana manokana (Ampio tsanganana - tsanganana manokana): and enter the name of the added column in the window that opens (for example, corrected address) and our magic function List.Accumulate:

Dia mijanona ny tsindrio eo OK – and we get a column with the replacements made:

Mariho fa:

• Since Power Query is case sensitive, there was no replacement in the penultimate line, because in the directory we have “SPb”, not “SPb”.
• If there are several substrings to replace at once in the source data (for example, in the 7th line you need to replace both “S-Pb” and “Prospectus”), then this does not create any problems (unlike replacing with formulas from the previous method).
• If there is nothing to replace in the source text (9th line), then no errors occur (unlike, again, from replacement by formulas).

The speed of such a request is very, very decent. For example, for a table of initial data with a size of 5000 rows, this query was updated in less than a second (without buffering, by the way, about 3 seconds!)

How the List.Accumulate function works

In principle, this could be the end (for me to write, and for you to read) this article. If you want to not only be able to, but also understand how it works “under the hood”, then you will have to dive a little deeper into the rabbit hole and deal with the List.Accumulate function, which did all the bulk replacement work for us.

Ny syntax ho an'ity asa ity dia:

=List.Accumulate(lisitra, voa, mpiorina)

izay

• lisitra is the list whose elements we are iterating over. 
• voa – initial state
• mpiorina – a function that performs some operation (mathematical, text, etc.) on the next element of the list and accumulates the result of processing in a special variable.
  

In general, the syntax for writing functions in Power Query looks like this:

(argument1, argument2, … argumentN) => some actions with arguments

For example, the summation function could be represented as:

(a, b) => a + b

For List.Accumulate , this accumulator function has two required arguments (they can be named anything, but the usual names are fanjakana и amin'izao fotoana izao, as in the official help for this function, where:

• fanjakana – a variable where the result is accumulated (its initial value is the one mentioned above voa)
  
• amin'izao fotoana izao – the next iterated value from the list lisitra
  

For example, let’s take a look at the steps of the logic of the following construction:

=List.Accumulate({3, 2, 5}, 10, (state, current) => state + current)

This is the latest accumulated fanjakana the value is our List.Accumulate function and outputs as a result:

If you fantasize a little, then using the List.Accumulate function, you can simulate, for example, the Excel function CONCATENATE (in Power Query, its analogue is called Text.Combine) using the expression:

Or even search for the maximum value (imitation of Excel’s MAX function, which in Power Query is called List.Max):

However, the main feature of List.Accumulate is the ability to process not only simple text or numeric lists as arguments, but more complex objects – for example, lists-from-lists or lists-from-records (hello, Directory!)

Let’s look again at the construction that performed the replacement in our problem:

List.Accumulate(Directory, [Address], (state,current) => Text.Replace(state, current[Find], current[Replace]) )

What is really going on here?

1. As initial value (voa) we take the first clumsy text from the column [Address] our table: 199034, St. Petersburg, str. Beringa, d. 1
2. Then List.Accumulate iterates over the elements of the list one by one – boky. Each element of this list is a record consisting of a pair of fields “What to find – What to replace with” or, in other words, the next line in the directory.
3. The accumulator function puts into a variable fanjakana initial value (first address 199034, St. Petersburg, str. Beringa, d. 1) and performs an accumulator function on it – the replacement operation using the standard M-function Text.Replace (analogous to Excel’s SUBSTITUTE function). Its syntax is:

   Text.Replace( original text, what we are looking for, what we are replacing with )

   and here we have:

• fanjakana is our dirty address, which lies in fanjakana (getting there from voa)
  
• current[Search] – field value Mahita from the next iterated entry of the list Directory, which lies in the variable amin'izao fotoana izao
• current[Replace] – field value mpisolo toerana from the next iterated entry of the list Directorylying in amin'izao fotoana izao

Thus, for each address, a full cycle of enumeration of all lines in the directory is run each time, replacing the text from the [Find] field with the value from the [Replace] field.

Hope you got the idea 🙂

• Bulk replace text in a list using formulas
  
• Fiteny mahazatra (RegExp) amin'ny Power Query