Panggantos teks akeh ing Power Query kanthi fungsi List.Accumulate

Carane cepet lan akeh ngganti teks miturut dhaptar referensi karo rumus - kita wis diurutake metu. Saiki ayo nyoba nindakake ing Power Query.

Kaya sing asring kedadeyan Nindakake tugas iki luwih gampang tinimbang nerangake kok kerjane, nanging ayo kang nyoba kanggo nindakake loro 🙂

Dadi, kita duwe rong tabel dinamis "pinter" sing digawe saka kisaran biasa kanthi trabasan keyboard Ctrl+T utawa tim Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel):

Aku nelpon meja pisanan Data, tabel kapindho - Direktoringgunakake lapangan Jeneng meja (jeneng tabel) tab Constructor (Desain).

Tugas: ngganti alamat ing tabel Data kabeh kedadeyan saka kolom Kanggo nemokake Buku Pegangan menyang mitra sing cocog sing bener saka kolom Ganti. Sisa teks ing sel kudu tetep ora kena.

Langkah 1. Muat direktori menyang Power Query lan nguripake menyang dhaptar

Sawise nyetel sel aktif ing sembarang panggonan ing tabel referensi, klik ing tab Data (Tanggal)utawa ing tab Pitakonan Daya (yen sampeyan duwe versi Excel lawas lan sampeyan nginstal Power Query minangka tambahan ing tab sing kapisah) ing tombol Saka meja / kisaran (Saka Tabel/Rentang).

Tabel referensi bakal dimuat menyang editor query Power Query:

Supaya ora ngganggu, langkah otomatis ditambahake jinis dipunéwahi (Jenis sing diganti) ing panel tengen, langkah-langkah sing ditrapake bisa dibusak kanthi aman, mung ninggalake langkah kasebut sumber (Sumber):

Saiki, kanggo nindakake transformasi lan panggantos luwih lanjut, kita kudu ngowahi tabel iki dadi dhaptar (dhaptar).

Digression lirik

• Tabel yaiku susunan rong dimensi sing kasusun saka sawetara larik lan kolom.
• Rekaman (Rekam) – senar array siji-dimensi, dumadi saka sawetara kolom-elemen karo jeneng, contone [Jeneng = "Masha", Jenis Kelamin = "f", Umur = 25]
  
• List - kolom array siji-dimensi, kalebu sawetara unsur, contone {1, 2, 3, 10, 42} or {"Iman Pangarep-arep Katresnan" }
  

Kanggo ngatasi masalah kita, kita bakal utamané kasengsem ing jinis List.

Trik ing kene yaiku dhaptar item ing Power Query bisa uga ora mung nomer banal utawa teks, nanging uga dhaptar utawa cathetan liyane. Ana ing dhaptar (dhaptar) sing angel, kalebu cathetan (cathetan) sing kudu diuripake direktori. Ing notasi sintaksis Power Query (entri ing kurung kotak, dhaptar ing kurung kriting) bakal katon kaya:

{

    [ Golek = "St. Petersburg", Ganti = "St. Petersburg" ] ,

    [ Golek = "St. Petersburg", Ganti = "St. Petersburg" ] ,

    [ Golek = "Petrus", Ganti = "St. Petersburg" ] ,

etc.

}

Transformasi kasebut ditindakake nggunakake fungsi khusus saka basa M sing dibangun ing Power Query - Tabel.ToRecords. Kanggo aplikasi langsung ing garis rumus, nambah fungsi iki kanggo kode langkah ana sumber.

Yaiku:

Sawise:

Sawise nambahake fungsi Table.ToRecords, tampilan tabel kita bakal diganti - bakal dadi dhaptar rekaman. Isi rekaman individu bisa dideleng ing sisih ngisor panel tampilan kanthi ngeklik ing latar mburi sel ing jejere tembung apa wae. Rekam (nanging ora ing tembung siji!)

Saliyane ing ndhuwur, iku ndadekake pangertèn kanggo nambah siji liyane stroke - kanggo cache (buffer) dhaftar kita digawe. Iki bakal meksa Power Query kanggo mbukak dhaptar panelusuran kita sapisan menyang memori lan ora ngetung maneh nalika mengko ngakses kanggo ngganti. Kanggo nindakake iki, bungkus rumus kita ing fungsi liyane - Daftar. Buffer:

Caching kuwi bakal nambah kacepetan banget (kanthi kaping pirang-pirang!) Kanthi jumlah data dhisikan sing bakal dibusak.

Iki ngrampungake preparation saka handbook.

Iku tetep kanggo klik ing Ngarep - Nutup lan Muat - Nutup lan Muat menyang… (Ngarep - Tutup&Muat - Tutup&Muat menyang ..), pilih opsi Mung nggawe sambungan (Mung nggawe sambungan) lan bali menyang Excel.

Langkah 2. Loading tabel data

Kabeh iku trite kene. Kaya sadurunge karo buku referensi, kita munggah menyang sembarang panggonan ing meja, klik ing tab Data tombol Saka Tabel / Range lan meja kita Data mlebu Power Query. Langkah otomatis ditambahake jinis dipunéwahi (Jenis sing diganti) sampeyan uga bisa mbusak:

Ora ana tumindak preparatory khusus sing kudu ditindakake, lan kita pindhah menyang sing paling penting.

Langkah 3. Nindakake panggantos nggunakake fungsi List.Accumulate

Ayo nambah kolom sing diwilang menyang tabel data nggunakake perintah kasebut Nambahake Kolom - Kolom Kustom (Tambah kolom - Kolom khusus): lan ketik jeneng kolom sing ditambahake ing jendhela sing mbukak (contone, alamat didandani) lan fungsi sihir kita Daftar.Akumulasi:

Iku tetep kanggo klik ing OK - lan kita entuk kolom kanthi panggantos sing digawe:

Elinga:

• Wiwit Power Query sensitif huruf cilik, ora ana panggantos ing baris penultimate, amarga ing direktori kita duwe "SPb", dudu "SPb".
• Yen ana sawetara substrings kanggo ngganti bebarengan ing data sumber (contone, ing baris 7th sampeyan kudu ngganti loro "S-Pb" lan "Prospectus"), banjur iki ora nggawe masalah (ora kaya ngganti karo rumus saka metode sadurunge).
• Yen ora ana sing bisa diganti ing teks sumber (baris 9), mula ora ana kesalahan (ora kaya, maneh, saka panggantos karo rumus).

Kacepetan panyuwunan kasebut pancen prayoga banget. Contone, kanggo tabel data awal kanthi ukuran 5000 larik, pitakon iki dianyari kurang saka detik (tanpa buffering, udakara 3 detik!)

Carane List.Accumulate fungsi dianggo

Ing asas, iki bisa dadi pungkasan (kanggo aku nulis, lan kanggo sampeyan maca) artikel iki. Yen sampeyan pengin ora mung bisa, nanging uga ngerti cara kerjane "ing hood", sampeyan kudu nyilem sethitik luwih jero menyang bolongan terwelu lan menehi hasil karo List.Accumulate fungsi, kang nindakake kabeh panggantos akeh. kerja kanggo kita.

Sintaks kanggo fungsi iki yaiku:

=Daftar.Akumulasi(dhaftar, winih, accumulator)

ngendi

• dhaftar iku dhaftar kang unsur kita iterating liwat. 
• winih - kahanan wiwitan
• accumulator - fungsi sing nindakake sawetara operasi (matematika, teks, etc.) ing unsur sabanjuré dhaftar lan accumulates asil Processing ing variabel khusus.
  

Umumé, sintaks kanggo nulis fungsi ing Power Query katon kaya iki:

(argument1, argument2, ... argumentN) => sawetara tumindak kanthi argumen

Contone, fungsi penjumlahan bisa dituduhake minangka:

(a, b) => a + b

Kanggo List.Accumulate, fungsi accumulator iki nduweni rong argumen sing dibutuhake (bisa dijenengi apa wae, nanging jeneng umume negara и saiki, kaya ing bantuan resmi kanggo fungsi iki, ing ngendi:

• negara – variabel ing ngendi asil dikumpulake (nilai wiwitane yaiku sing kasebut ing ndhuwur winih)
  
• saiki – Nilai iterated sabanjuré saka dhaftar dhaftar
  

Contone, ayo goleki langkah-langkah logika konstruksi ing ngisor iki:

=Daftar.Akumulasi({3, 2, 5}, 10, (negara, saiki) => negara + saiki)

Iki akumulasi paling anyar negara Nilai kasebut minangka List.Accumulate fungsi lan output minangka asil:

Yen sampeyan fantasize sethithik, banjur nggunakake fungsi List.Accumulate, sampeyan bisa simulasi, contone, fungsi Excel CONCATENATE (ing Power Query, analog kasebut diarani Teks. Gabungan) nggunakake ekspresi:

Utawa malah nelusuri nilai maksimum (tiruan fungsi MAX Excel, sing ing Power Query diarani Daftar. Maks):

Nanging, fitur utama List.Accumulate yaiku kemampuan kanggo ngolah ora mung teks prasaja utawa dhaptar numerik minangka argumen, nanging obyek sing luwih rumit - contone, dhaptar-saka-dhaptar utawa dhaptar-saka-cathetan (halo, Direktori!)

Ayo goleki maneh konstruksi sing nindakake panggantos ing masalah kita:

Daftar. Akumulasi(Direktori, [Alamat], (state,current) => Text.Ganti(status, saiki[Ganti], saiki[Ganti]) )

Ana apa tenan ing kene?

1. Minangka nilai awal (winih) kita njupuk teks kikuk pisanan saka kolom [Alamat] meja kita: 199034, St. Petersburg, str. Beringa, d. 1
2. Banjur List.Accumulate iterates liwat unsur dhaptar siji-siji - Buku Pegangan. Saben unsur dhaptar iki minangka rekaman sing dumadi saka sepasang kolom "Apa sing bakal ditemokake - Apa sing kudu diganti" utawa, kanthi tembung liya, baris sabanjure ing direktori kasebut.
3. Fungsi accumulator sijine menyang variabel negara nilai wiwitan (alamat pisanan 199034, St. Petersburg, str. Beringa, d. 1) lan nindakake fungsi akumulator - operasi panggantos nggunakake fungsi M standar Teks.Ganti (analog karo fungsi SUBSTITUTE Excel). Sintaksipun inggih punika:

   Text.Ganti (teks asli, apa sing kita goleki, apa sing kita ganti)

   lan ing kene kita duwe:

• negara punika alamat reged kita, kang dumunung ing negara (tekan kono saka winih)
  
• saiki [Telusuri] - Nilai lapangan Kanggo nemokake saka entri iterated sabanjuré saka dhaftar Direktori, sing dumunung ing variabel saiki
• saiki [Ganti] - Nilai lapangan Ganti saka entri iterated sabanjuré saka dhaftar Direktoringapusi ing saiki

Mangkono, kanggo saben alamat, siklus lengkap enumerasi kabeh baris ing direktori ditindakake saben wektu, ngganti teks saka kolom [Ganti] karo nilai saka kolom [Ganti].

Muga-muga sampeyan entuk ide 🙂

• Ganti teks kanthi akeh ing dhaptar kanthi nggunakake rumus
  
• Ekspresi Reguler (RegExp) ing Power Query