Lielapjoma teksta aizstāšana programmā Power Query ar funkciju List.Accumulate

Kā ātri un vairumā aizstāt tekstu pēc uzziņu saraksta ar formulām – to jau esam sakārtojuši. Tagad mēģināsim to izdarīt programmā Power Query.

Kā tas bieži notiek izpildīt šis uzdevums ir daudz vienkāršāks nekā izskaidrot kāpēc tas darbojas, bet mēģināsim darīt abus 🙂

Tātad mums ir divas “gudras” dinamiskas tabulas, kas izveidotas no parastajiem diapazoniem ar īsinājumtaustiņu Ctrl+T vai komanda Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu):

Zvanīju uz pirmo galdu Datums, otrā tabula - Direktorijaizmantojot lauku Tabulas nosaukums (Tabulas nosaukums) tab Konstruktors (Dizains).

Uzdevums: aizstāt adreses tabulā Datums visi gadījumi no kolonnas Atrast Rokasgrāmata to atbilstošajiem pareizajiem kolēģiem no kolonnas Aizstājējs. Pārējam tekstam šūnās jāpaliek neskartam.

1. darbība. Ielādējiet direktoriju programmā Power Query un pārveidojiet to par sarakstu

Iestatiet aktīvo šūnu jebkurā atsauces tabulas vietā, noklikšķiniet uz cilnes Datums (Datums)vai cilnē Jaudas vaicājums (ja jums ir veca Excel versija un Power Query instalējāt kā pievienojumprogrammu atsevišķā cilnē) uz pogas No galda/diapazona (No tabulas/diapazona).

Atsauces tabula tiks ielādēta Power Query vaicājumu redaktorā:

Lai netraucētu, automātiski pievienots solis modificēts tips (Mainīts veids) labajā panelī lietotās darbības var droši dzēst, atstājot tikai soli avots (Avots):

Tagad, lai veiktu turpmākas transformācijas un nomaiņas, mums šī tabula jāpārvērš par sarakstu (sarakstu).

Liriska atkāpe

• Tabula ir divdimensiju masīvs, kas sastāv no vairākām rindām un kolonnām.
• Ierakstīt (ieraksts) – viendimensijas masīva virkne, kas sastāv no vairākiem laukiem-elementiem ar nosaukumiem, piemēram, [Vārds = "Maša", dzimums = "f", vecums = 25]
  
• saraksts – viendimensijas masīvs-kolonna, kas sastāv, piemēram, no vairākiem elementiem {1, 2, 3, 10, 42} or { "Ticība, cerība mīlestība" }
  

Lai atrisinātu mūsu problēmu, mūs galvenokārt interesēs veids saraksts.

Šis triks ir tāds, ka Power Query saraksta vienumi var būt ne tikai banāli skaitļi vai teksts, bet arī citi saraksti vai ieraksti. Tieši tik sarežģītā sarakstā (sarakstā), kas sastāv no ierakstiem (ierakstiem), mums ir jāpārvērš mūsu direktorijs. Power Query sintaktiskajā apzīmējumā (ieraksti kvadrātiekavās, saraksti cirtainajās iekavās) tas izskatītos šādi:

{

    [ Atrast = "Sv. Pēterburga”, Aizstāt = “St. Pēterburga”] ,

    [ Atrast = "Sv. Pēterburga”, Aizstāt = “St. Pēterburga”] ,

    [ Atrast = "Pēteris", Aizstāt = "Sv. Pēterburga”] ,

un tā joprojām

}

Šāda transformācija tiek veikta, izmantojot īpašu Power Query iebūvētās M valodas funkciju - Tabula.ToRecords. Lai to lietotu tieši formulas joslā, pievienojiet šo funkciju tur esošajam soļa kodam avots.

Tas bija:

Pēc:

Pēc funkcijas Table.ToRecords pievienošanas mūsu tabulas izskats mainīsies – tā pārvērtīsies par ierakstu sarakstu. Atsevišķu ierakstu saturu var redzēt skata rūts apakšā, noklikšķinot šūnas fonā blakus jebkuram vārdam Ieraksts (bet ne vienā vārdā!)

Papildus iepriekšminētajam ir jēga pievienot vēl vienu insultu - mūsu izveidotā saraksta kešatmiņai (buferēšanai). Tas liks Power Query vienreiz ielādēt mūsu uzmeklēšanas sarakstu atmiņā un nepārrēķināt to vēlreiz, kad vēlāk piekļūstam tam, lai to aizstātu. Lai to izdarītu, iesaiņojiet mūsu formulu citā funkcijā - Saraksts. Buferis:

Šāda kešatmiņa nodrošinās ļoti ievērojamu ātruma pieaugumu (vairākas reizes!) ar lielu sākotnējo datu apjomu, kas tiks notīrīts.

Tas pabeidz rokasgrāmatas sagatavošanu.

Atliek noklikšķināt Sākums — aizvērt un ielādēt — aizvērt un ielādēt… (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt uz...), atlasiet opciju Vienkārši izveidojiet savienojumu (Izveidojiet tikai savienojumu) un atgriezieties programmā Excel.

2. darbība. Datu tabulas ielāde

Šeit viss ir banāls. Tāpat kā iepriekš ar atsauces grāmatu, mēs nokļūstam jebkurā tabulas vietā, noklikšķiniet uz cilnes Datums poga No tabulas/diapazona un mūsu galds Datums nokļūst Power Query. Automātiski pievienots solis modificēts tips (Mainīts veids) varat arī noņemt:

Ar to nav jāveic nekādas īpašas sagatavošanas darbības, un mēs pārejam pie vissvarīgākās lietas.

3. darbība. Veiciet nomaiņu, izmantojot funkciju List.Accumulate

Pievienosim mūsu datu tabulai aprēķināto kolonnu, izmantojot komandu Kolonnas pievienošana — pielāgota kolonna (Pievienot kolonnu — pielāgota kolonna): un atvērtajā logā ievadiet pievienotās kolonnas nosaukumu (piemēram, labota adrese) un mūsu maģiskā funkcija Saraksts.Uzkrāt:

Atliek noklikšķināt OK - un mēs iegūstam kolonnu ar veiktajām nomaiņām:

Pieraksti to:

• Tā kā Power Query ir reģistrjutīgs, priekšpēdējā rindā netika aizstāts, jo direktorijā mums ir “SPb”, nevis “SPb”.
• Ja avota datos ir jāaizstāj vairākas apakšvirknes vienlaikus (piemēram, 7. rindā jāaizstāj gan “S-Pb”, gan “Prospekts”), tas nerada nekādas problēmas (atšķirībā no aizstāšanas ar formulām no iepriekšējā metode).
• Ja avota tekstā nav ko aizstāt (9. rindiņa), tad kļūdas nerodas (atšķirībā no aizstāšanas ar formulām).

Šāda pieprasījuma ātrums ir ļoti, ļoti pieklājīgs. Piemēram, sākotnējo datu tabulai, kuras lielums ir 5000 rindas, šis vaicājums tika atjaunināts mazāk nekā sekundē (bez buferizācijas, starp citu, apmēram 3 sekundes!)

Kā darbojas funkcija List.Accumulate

Principā ar to šis raksts varētu beigties (lai es rakstītu, bet jūs lasītu). Ja vēlaties ne tikai paspēt, bet arī saprast, kā tas darbojas “zem motora pārsega”, tad nāksies nedaudz ienirt truša bedrē un nodarboties ar funkciju List.Accumulate, kas veica visu masveida nomaiņu. strādāt pie mums.

Šīs funkcijas sintakse ir šāda:

=List.Accumulate(saraksts, sēkla, Akumulators)

kur

• saraksts ir saraksts, kura elementus mēs atkārtojam. 
• sēkla - sākotnējais stāvoklis
• Akumulators – funkcija, kas veic kādu darbību (matemātisko, teksta u.c.) ar nākamo saraksta elementu un uzkrāj apstrādes rezultātu īpašā mainīgajā.
  

Kopumā Power Query funkciju rakstīšanas sintakse izskatās šādi:

(arguments1, arguments2, … argumentsN) => dažas darbības ar argumentiem

Piemēram, summēšanas funkciju var attēlot šādi:

(a, b) => a + b

List.Accumulate šai akumulatora funkcijai ir divi obligātie argumenti (tos var nosaukt jebko, bet parastie nosaukumi ir bija и strāva, kā šīs funkcijas oficiālajā palīdzībā, kur:

• bija – mainīgais, kurā tiek uzkrāts rezultāts (tā sākotnējā vērtība ir iepriekš minētā sēkla)
  
• strāva – nākamā iterētā vērtība no saraksta saraksts
  

Piemēram, apskatīsim šādas konstrukcijas loģikas soļus:

=List.Accumulate({3, 2, 5}, 10, (stāvoklis, strāva) => stāvoklis + strāva)

Šis ir pēdējais uzkrātais bija vērtība ir mūsu List.Accumulate funkcija un izvadi kā rezultātā:

Ja nedaudz fantazē, tad, izmantojot funkciju List.Accumulate, var simulēt, piemēram, Excel funkciju CONCATENATE (programmā Power Query tās analogs tiek saukts Teksts.Apvienot) izmantojot izteicienu:

Vai pat meklējiet maksimālo vērtību (Excel funkcijas MAX imitācija, kas tiek saukta programmā Power Query Saraksts.Maks):

Taču List.Accumulate galvenā iezīme ir iespēja kā argumentus apstrādāt ne tikai vienkāršu tekstu vai ciparu sarakstus, bet arī sarežģītākus objektus – piemēram, lists-from-lists vai lists-from-records (sveiki, Directory!)

Apskatīsim vēlreiz konstrukciju, kas veica nomaiņu mūsu uzdevumā:

List.Accumulate(Direktorija, [Adrese], (stāvoklis,pašreizējais) => Teksts.Aizstāt(stāvoklis, pašreizējais[Atrast], pašreizējais[Aizstāt]) )

Kas te īsti notiek?

1. Kā sākotnējā vērtība (sēkla) ņemam no kolonnas pirmo neveiklo tekstu [Adrese] mūsu galds: 199034, Sanktpēterburga, str. Beringa, dz. 1
2. Pēc tam List.Accumulate atkārto saraksta elementus pa vienam – Rokasgrāmata. Katrs šī saraksta elements ir ieraksts, kas sastāv no lauku pāra “Ko atrast – ar ko aizstāt” jeb, citiem vārdiem sakot, nākamās rindiņas direktorijā.
3. Akumulatora funkcija ievieto mainīgo bija sākotnējā vērtība (pirmā adrese 199034, Sanktpēterburga, str. Beringa, dz. 1) un veic uz tā akumulatora funkciju – nomaiņas darbību, izmantojot standarta M-funkciju Teksts.Aizstāt (analogs Excel funkcijai SUBSTITUTE). Tās sintakse ir:

   Text.Replace (sākotnējais teksts, ko mēs meklējam, ko mēs aizstājam)

   un šeit mums ir:

• bija ir mūsu netīrā adrese, kas atrodas bija (nokļūstot no sēkla)
  
• pašreizējais[Meklēt] - lauka vērtība Atrast no nākamā atkārtotā saraksta ieraksta Direktorija, kas atrodas mainīgajā strāva
• pašreizējais[Aizstāt] - lauka vērtība Aizstājējs no nākamā atkārtotā saraksta ieraksta Direktorijaguļot strāva

Tādējādi katrai adresei katru reizi tiek izpildīts pilns visu direktorija rindu uzskaitīšanas cikls, aizstājot tekstu no lauka [Atrast] ar vērtību no lauka [Aizstāt].

Cerams, ka sapratāt ideju 🙂

• Lielapjoma teksta aizstāšana sarakstā, izmantojot formulas
  
• Regulārās izteiksmes (RegExp) programmā Power Query