ការជំនួសអត្ថបទច្រើននៅក្នុង Power Query ជាមួយមុខងារ 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 អនុវត្ត 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 បញ្ជា (Ctrl)+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 ជំនួស. 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 ប្រភព (ប្រភព):

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

ការបំប្លែងសារអត្ថបទ

• តារាង 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 {៥០, ៥០, ៥០, ០, ០} 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({៧, ៨, ៩}, 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 ជំនួស 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
  
• កន្សោមធម្មតា (RegExp) នៅក្នុងសំណួរថាមពល