Maramihang pagpapalit ng text sa Power Query na may function na List.Accumulate

Paano mabilis at maramihang palitan ang teksto ayon sa listahan ng sanggunian ng mga formula - naayos na namin ito. Ngayon subukan nating gawin ito sa Power Query.

Gaya ng madalas mangyari gumanap ang gawaing ito ay mas madali kaysa sa pagpapaliwanag bakit ito ay gumagana, ngunit subukan nating gawin pareho 🙂

Kaya, mayroon kaming dalawang "matalinong" dynamic na talahanayan na ginawa mula sa mga ordinaryong hanay na may keyboard shortcut Ctrl+T o pangkat Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan):

Tinawag ko ang unang table data, ang pangalawang talahanayan - Directorygamit ang bukid Pangalan ng talahanayan (Pangalan ng talahanayan) tab Tagagawa (Disenyo).

Gawain: palitan ang mga address sa talahanayan data lahat ng mga pangyayari mula sa isang column Hanapin Manwal sa kanilang mga kaukulang tamang katapat mula sa hanay Kapalit. Ang natitirang bahagi ng teksto sa mga cell ay dapat manatiling hindi nagalaw.

Hakbang 1. I-load ang direktoryo sa Power Query at gawin itong isang listahan

Kapag naitakda ang aktibong cell sa anumang lugar sa reference table, mag-click sa tab data (Petsa)o sa tab kapangyarihan query (kung mayroon kang lumang bersyon ng Excel at na-install mo ang Power Query bilang add-in sa isang hiwalay na tab) sa button Mula sa talahanayan/saklaw (Mula sa Talahanayan/Hanay).

Ilo-load ang reference table sa editor ng query ng Power Query:

Upang hindi makagambala, isang awtomatikong idinagdag na hakbang binagong uri (Binago ang Uri) sa kanang panel, ang mga inilapat na hakbang ay maaaring ligtas na matanggal, na iiwan lamang ang hakbang pinagmulan (Pinagmulan):

Ngayon, para magsagawa ng karagdagang pagbabago at pagpapalit, kailangan nating gawing listahan (listahan) ang talahanayang ito.

Lirikal na paghihirap

• mesa ay isang two-dimensional array na binubuo ng ilang row at column.
• Record (Record) – one-dimensional array-string, na binubuo ng ilang mga field-element na may mga pangalan, halimbawa [Pangalan = “Masha”, Kasarian = “f”, Edad = 25]
  
• listahan – isang one-dimensional na array-column, na binubuo ng ilang elemento, halimbawa {1, 2, 3, 10, 42} or { "Pananampalataya, pag-asa, pag-ibig" }
  

Upang malutas ang aming problema, kami ay pangunahing magiging interesado sa uri listahan.

Ang trick dito ay ang mga listahan ng item sa Power Query ay maaaring hindi lamang mga banal na numero o teksto, kundi pati na rin ang iba pang mga listahan o talaan. Ito ay nasa isang mapanlinlang na listahan (listahan), na binubuo ng mga talaan (mga talaan) na kailangan naming i-on ang aming direktoryo. Sa Power Query syntactic notation (mga entry sa square bracket, mga listahan sa curly bracket) ganito ang magiging hitsura:

{

    [ Hanapin = "St. Petersburg", Palitan = "St. Petersburg” ] ,

    [ Hanapin = "St. Petersburg", Palitan = "St. Petersburg” ] ,

    [ Hanapin = "Peter", Palitan = "St. Petersburg” ] ,

at iba pa

}

Ang ganitong pagbabago ay isinasagawa gamit ang isang espesyal na function ng wikang M na binuo sa Power Query - Table.ToRecords. Upang direktang ilapat ito sa formula bar, idagdag ang function na ito sa step code doon pinagmulan.

Ito ay:

Pagkatapos:

Pagkatapos idagdag ang function na Table.ToRecords, magbabago ang hitsura ng aming table – ito ay magiging isang listahan ng mga record. Ang mga nilalaman ng mga indibidwal na tala ay makikita sa ibaba ng view pane sa pamamagitan ng pag-click sa background ng cell sa tabi ng anumang salita rekord (ngunit hindi sa isang salita!)

Bilang karagdagan sa nasa itaas, makatuwirang magdagdag ng isa pang stroke – upang i-cache (buffer) ang aming ginawang listahan. Pipilitin nito ang Power Query na i-load ang aming listahan ng paghahanap nang isang beses sa memorya at hindi na muling kalkulahin ito kapag na-access namin ito sa ibang pagkakataon upang palitan ito. Upang gawin ito, balutin ang aming formula sa isa pang function - Listahan.Buffer:

Ang ganitong pag-cache ay magbibigay ng isang kapansin-pansing pagtaas ng bilis (sa ilang beses!) na may malaking halaga ng paunang data na tatanggalin.

Kinukumpleto nito ang paghahanda ng handbook.

Ito ay nananatiling mag-click sa Home – Isara at I-load – Isara at I-load sa… (Home — Isara&I-load — Isara&I-load sa..), Pumili ng opsyon Gumawa lang ng koneksyon (Gumawa lamang ng koneksyon) at bumalik sa Excel.

Hakbang 2. Nilo-load ang talahanayan ng data

Ang lahat ay baliw dito. Tulad ng dati sa reference book, nakakarating kami sa anumang lugar sa talahanayan, mag-click sa tab data butones Mula sa Table/Range at ang table namin data pumapasok sa Power Query. Awtomatikong idinagdag na hakbang binagong uri (Binago ang Uri) maaari mo ring alisin:

Walang mga espesyal na aksyon sa paghahanda ang kinakailangan na gawin dito, at lumipat tayo sa pinakamahalagang bagay.

Hakbang 3. Magsagawa ng mga pagpapalit gamit ang List.Accumulate function

Magdagdag tayo ng kalkuladong column sa aming data table gamit ang command Pagdaragdag ng Column – Custom Column (Magdagdag ng column — Custom na column): at ilagay ang pangalan ng idinagdag na column sa window na bubukas (halimbawa, naitama ang address) at ang aming magic function Listahan.Mag-ipon:

Ito ay nananatiling mag-click sa OK – at nakakakuha kami ng column na may mga ginawang kapalit:

Tandaan na:

• Dahil case sensitive ang Power Query, walang kapalit sa penultimate line, dahil sa direktoryo mayroon kaming "SPb", hindi "SPb".
• Kung mayroong ilang mga substring na papalitan nang sabay-sabay sa source data (halimbawa, sa ika-7 linya kailangan mong palitan ang parehong "S-Pb" at "Prospectus"), hindi ito lumilikha ng anumang mga problema (hindi tulad ng pagpapalit ng mga formula mula sa ang nakaraang pamamaraan).
• Kung walang mapapalitan sa pinagmulang teksto (ika-9 na linya), walang mga error na magaganap (hindi katulad, muli, mula sa pagpapalit ng mga formula).

Ang bilis ng naturang kahilingan ay napaka, napaka disente. Halimbawa, para sa isang talahanayan ng paunang data na may sukat na 5000 row, ang query na ito ay na-update nang wala pang isang segundo (nang walang buffering, sa pamamagitan ng paraan, mga 3 segundo!)

Paano gumagana ang List.Accumulate function

Sa prinsipyo, maaaring ito na ang wakas (para magsulat ako, at mabasa mo) ang artikulong ito. Kung nais mong hindi lamang magawa, ngunit maunawaan din kung paano ito gumagana "sa ilalim ng talukbong", pagkatapos ay kailangan mong sumisid nang kaunti sa butas ng kuneho at harapin ang List.Accumulate function, na ginawa ang lahat ng maramihang pagpapalit magtrabaho para sa amin.

Ang syntax para sa function na ito ay:

=List. Accumulate(listahan, magbigay ng binhi, nagtitipon)

saan

• listahan ay ang listahan kung saan ang mga elemento ay inuulit namin. 
• magbigay ng binhi - panimulang estado
• nagtitipon – isang function na nagsasagawa ng ilang operasyon (matematika, teksto, atbp.) sa susunod na elemento ng listahan at nag-iipon ng resulta ng pagproseso sa isang espesyal na variable.
  

Sa pangkalahatan, ganito ang hitsura ng syntax para sa pagsusulat ng mga function sa Power Query:

(argument1, argument2, … argumentN) => ilang aksyon na may mga argumento

Halimbawa, ang summation function ay maaaring katawanin bilang:

(a, b) => a + b

Para sa List.Accumulate , ang function ng accumulator na ito ay may dalawang kinakailangang argumento (maaari silang pangalanan kahit ano, ngunit ang karaniwang mga pangalan ay ay и kasalukuyan, tulad ng sa opisyal na tulong para sa function na ito, kung saan:

• ay – isang variable kung saan ang resulta ay naipon (ang paunang halaga nito ay ang nabanggit sa itaas magbigay ng binhi)
  
• kasalukuyan – ang susunod na inuulit na halaga mula sa listahan listahan
  

Halimbawa, tingnan natin ang mga hakbang ng lohika ng sumusunod na konstruksyon:

=List. Accumulate({3, 2, 5}, 10, (estado, kasalukuyang) => estado + kasalukuyang)

Ito ang pinakabagong naipon ay ang halaga ay ang aming Listahan. Mag-ipon ng function at mga output bilang isang resulta:

Kung nagpapantasya ka ng kaunti, pagkatapos ay gamit ang List.Accumulate function, maaari mong gayahin, halimbawa, ang Excel function CONCATENATE (sa Power Query, ang analogue nito ay tinatawag Teksto.Pagsamahin) gamit ang expression:

O kahit na maghanap para sa maximum na halaga (imitasyon ng MAX function ng Excel, na tinatawag sa Power Query Listahan.Max):

Gayunpaman, ang pangunahing tampok ng List.Accumulate ay ang kakayahang magproseso hindi lamang ng mga simpleng text o numeric na listahan bilang mga argumento, ngunit mas kumplikadong mga bagay – halimbawa, lists-from-lists o lists-from-records (hello, Directory!)

Tingnan natin muli ang konstruksyon na nagsagawa ng kapalit sa ating problema:

List. Accumulate(Directory, [Address], (estado,kasalukuyan) => Text.Palitan(estado, kasalukuyang[Hanapin], kasalukuyang[Palitan]) )

Ano ba talaga ang nangyayari dito?

1. Bilang paunang halaga (magbigay ng binhi) kinukuha namin ang unang clumsy na text mula sa column [Address] aming mesa: 199034, St. Petersburg, str. Beringa, d. 1
2. Pagkatapos ang List.Accumulate ay umuulit sa mga elemento ng listahan nang paisa-isa – Manwal. Ang bawat elemento ng listahang ito ay isang talaan na binubuo ng isang pares ng mga field na "Ano ang hahanapin - Ano ang papalitan" o, sa madaling salita, ang susunod na linya sa direktoryo.
3. Ang function ng accumulator ay inilalagay sa isang variable ay paunang halaga (unang address 199034, St. Petersburg, str. Beringa, d. 1) at nagsasagawa ng function ng accumulator dito - ang pagpapalit na operasyon gamit ang karaniwang M-function Text.Palitan (katulad sa SUBSTITUTE function ng Excel). Ang syntax nito ay:

   Text.Replace( orihinal na text, kung ano ang hinahanap namin, kung ano ang pinapalitan namin )

   at narito tayo:

• ay ay ang aming maruming address, na nasa ay (pagpunta doon mula sa magbigay ng binhi)
  
• kasalukuyang [Paghahanap] – halaga ng field Hanapin mula sa susunod na inuulit na entry ng listahan Directory, na nasa variable kasalukuyan
• kasalukuyang [Palitan] – halaga ng field Kapalit mula sa susunod na inuulit na entry ng listahan Directorynakahiga sa kasalukuyan

Kaya, para sa bawat address, ang isang buong cycle ng enumeration ng lahat ng mga linya sa direktoryo ay pinapatakbo sa bawat oras, na pinapalitan ang teksto mula sa field na [Hanapin] ng halaga mula sa field na [Palitan].

Sana nakuha mo ang ideya 🙂

• Maramihang palitan ang text sa isang listahan gamit ang mga formula
  
• Mga Regular na Expression (RegExp) sa Power Query