פאַרנעם טעקסט פאַרבייַט אין פּאָווער קווערי מיט ליסט.אַקומולאַטע פֿונקציע

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 דורכפירן this task is much easier than explaining פאַר וואָס it works, but let’s try to do both 🙂

So, we have two “smart” dynamic tables created from ordinary ranges with a keyboard shortcut קטרל+T אָדער מאַנשאַפֿט היים - פֿאָרמאַט ווי אַ טיש (היים - פֿאָרמאַט ווי טיש):

I called the first table דאַטע, the second table – דירעקטאריניצן פעלד טיש נאָמען (Table name) קוויטל קאָנסטרוקטאָר (פּלאַן).

Task: replace in addresses in the table דאַטע all occurrences from a column צו געפינען האַנדבאָאָק to their corresponding correct counterparts from the column Substitute. 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 דאַטע (דאַטע)אָדער אויף די בלאַט מאַכט קווערי (if you have an old version of Excel and you installed Power Query as an add-in on a separate tab) on the button פון טיש / קייט (פון טיש / קייט).

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

In order not to interfere, an automatically added step מאַדאַפייד טיפּ (געענדערט טיפּ) in the right panel, the applied steps can be safely deleted, leaving only the step מאָקער (Source):

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

Lyrical digression

• טיש 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]
  
• רעשימע – a one-dimensional array-column, consisting of several elements, for example {1} or { “Faith Hope Love” }
  

To solve our problem, we will be primarily interested in the type רעשימע.

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” ] ,

אאז"ו ו

}

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 מאָקער.

עס איז געווען:

נאָך:

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 רעקאָרד (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.

עס בלייבט צו גיט אויף Home – Close and Load – Close and Load to… (היים - נאָענט & לאָדן - נאָענט & לאָדן צו ..), אויסקלייַבן אַן אָפּציע נאָר שאַפֿן אַ קשר (נאָר שאַפֿן קשר) 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 דאַטע קנעפּל פֿון טיש / קייט and our table דאַטע gets into Power Query. Automatically added step מאַדאַפייד טיפּ (געענדערט טיפּ) 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 אַדינג אַ זייַל - מנהג זייַל (לייג זייַל — מנהג זייַל): and enter the name of the added column in the window that opens (for example, corrected address) and our magic function List.Accumulate:

עס בלייבט צו גיט אויף OK – and we get a column with the replacements made:

באמערק אז:

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

דער סינטאַקס פֿאַר דעם פֿונקציע איז:

=List.Accumulate(רעשימע, זוימען, אַקיומיאַלייטער)

ווו

• רעשימע is the list whose elements we are iterating over. 
• זוימען – initial state
• אַקיומיאַלייטער – 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 זענען и קראַנט, as in the official help for this function, where:

• זענען – a variable where the result is accumulated (its initial value is the one mentioned above זוימען)
  
• קראַנט – the next iterated value from the list רעשימע
  

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

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

This is the latest accumulated זענען 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 טעקסט.קאָמביין) 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(דירעקטארי, [אַדרעס], (state,current) => Text.Replace(state, current[Find], current[Replace]) )

What is really going on here?

1. As initial value (זוימען) we take the first clumsy text from the column [אַדרעס] our table: 199034, St. Petersburg, str. Beringa, d. 1
2. Then List.Accumulate iterates over the elements of the list one by one – האַנדבאָאָק. 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 זענען 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:

• זענען is our dirty address, which lies in זענען (getting there from זוימען)
  
• current[Search] – field value צו געפינען from the next iterated entry of the list דירעקטארי, which lies in the variable קראַנט
• current[Replace] – field value Substitute from the next iterated entry of the list דירעקטאריlying in קראַנט

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
  
• רעגולער עקספּרעססיאָנס (רעגעקספּ) אין מאַכט אָנפֿרעג