Penggantian teks pukal dalam Power Query dengan fungsi List.Accumulate

Bagaimana untuk menggantikan teks secara cepat dan pukal mengikut senarai rujukan dengan formula – kami telah menyusunnya. Sekarang mari cuba lakukannya dalam Power Query.

Seperti yang sering berlaku melaksanakan tugas ini lebih mudah daripada menerangkan mengapa ia berfungsi, tetapi mari kita cuba lakukan kedua-duanya 🙂

Jadi, kami mempunyai dua jadual dinamik "pintar" yang dibuat daripada julat biasa dengan pintasan papan kekunci Ctrl+T atau pasukan Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual):

Saya memanggil meja pertama Tarikh, jadual kedua - Direktorimenggunakan bidang Nama jadual (Nama jadual) tab Pembina (Reka Bentuk).

Tugas: gantikan alamat dalam jadual Tarikh semua kejadian daripada lajur Untuk mencari Buku panduan kepada rakan sejawatan betul yang sepadan dari lajur Pengganti. Selebihnya teks dalam sel harus kekal tidak disentuh.

Langkah 1. Muatkan direktori ke dalam Power Query dan ubahnya menjadi senarai

Setelah menetapkan sel aktif ke mana-mana tempat dalam jadual rujukan, klik pada tab Tarikh (Tarikh)atau pada tab Kuasa Kuasa (jika anda mempunyai versi lama Excel dan anda memasang Power Query sebagai tambahan pada tab berasingan) pada butang Daripada jadual/julat (Dari Jadual/Julat).

Jadual rujukan akan dimuatkan ke dalam editor pertanyaan Power Query:

Untuk tidak mengganggu, langkah ditambah secara automatik jenis yang diubah suai (Jenis Ditukar) di panel kanan, langkah yang digunakan boleh dipadamkan dengan selamat, hanya meninggalkan langkah Source (Sumber):

Sekarang, untuk melakukan transformasi dan penggantian selanjutnya, kita perlu menukar jadual ini menjadi senarai (senarai).

Pencerobohan lirik

• Jadual ialah tatasusunan dua dimensi yang terdiri daripada beberapa baris dan lajur.
• Rekod (Rekod) – rentetan tatasusunan satu dimensi, yang terdiri daripada beberapa elemen medan dengan nama, contohnya [Nama = “Masha”, Jantina = “f”, Umur = 25]
  
• senarai – lajur tatasusunan satu dimensi, yang terdiri daripada beberapa elemen, sebagai contoh {1, 2, 3, 10, 42} or { "Iman harapan cinta" }
  

Untuk menyelesaikan masalah kami, kami akan berminat dengan jenisnya senarai.

Caranya di sini ialah item senarai dalam Power Query bukan sahaja nombor atau teks biasa, tetapi juga senarai atau rekod lain. Ia berada dalam senarai (senarai) yang rumit, yang terdiri daripada rekod (rekod) yang kami perlukan untuk menukar direktori kami. Dalam notasi sintaksis Power Query (entri dalam kurungan segi empat sama, senarai dalam kurungan kerinting) ini akan kelihatan seperti:

{

    [ Cari = "St. Petersburg", Gantikan = "St. Petersburg” ] ,

    [ Cari = "St. Petersburg", Gantikan = "St. Petersburg” ] ,

    [ Cari = "Peter", Gantikan = "St. Petersburg” ] ,

dan lain-lain.

}

Transformasi sedemikian dilakukan menggunakan fungsi khas bahasa M yang dibina ke dalam Power Query - Jadual.ToRecords. Untuk menggunakannya terus dalam bar formula, tambahkan fungsi ini pada kod langkah di sana Source.

Ia adalah:

Selepas:

Selepas menambah fungsi Table.ToRecords, penampilan jadual kami akan berubah – ia akan bertukar menjadi senarai rekod. Kandungan rekod individu boleh dilihat di bahagian bawah anak tetingkap paparan dengan mengklik di latar belakang sel di sebelah mana-mana perkataan rekod (tetapi tidak dalam satu perkataan!)

Sebagai tambahan kepada perkara di atas, masuk akal untuk menambah satu pukulan lagi - untuk cache (penampan) senarai yang kami buat. Ini akan memaksa Power Query untuk memuatkan senarai carian kami sekali ke dalam memori dan tidak mengiranya semula apabila kami kemudian mengaksesnya untuk menggantikannya. Untuk melakukan ini, bungkus formula kami dalam fungsi lain - Senarai. Penampan:

Caching sedemikian akan memberikan peningkatan kelajuan yang sangat ketara (beberapa kali ganda!) dengan sejumlah besar data awal untuk dibersihkan.

Ini melengkapkan penyediaan buku panduan.

Ia kekal untuk mengklik Laman Utama – Tutup dan Muatkan – Tutup dan Muatkan ke… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke..), pilih satu pilihan Buat sambungan sahaja (Hanya buat sambungan) dan kembali ke Excel.

Langkah 2. Memuatkan jadual data

Semuanya basi di sini. Seperti sebelum ini dengan buku rujukan, kami bangun ke mana-mana tempat dalam jadual, klik pada tab Tarikh butang Daripada Jadual/Julat dan meja kami Tarikh masuk ke Power Query. Langkah ditambah secara automatik jenis yang diubah suai (Jenis Ditukar) anda juga boleh mengalih keluar:

Tiada tindakan persediaan khas diperlukan untuk dilakukan dengannya, dan kita beralih kepada perkara yang paling penting.

Langkah 3. Lakukan penggantian menggunakan fungsi List.Accumulate

Mari tambah lajur yang dikira pada jadual data kami menggunakan arahan Menambah Lajur – Lajur Tersuai (Tambah lajur — Lajur tersuai): dan masukkan nama lajur yang ditambahkan dalam tetingkap yang terbuka (contohnya, alamat yang diperbetulkan) dan fungsi ajaib kami Senaraikan.Kumpul:

Ia kekal untuk mengklik OK – dan kami mendapat lajur dengan penggantian yang dibuat:

Perhatikan bahawa:

• Memandangkan Power Query sensitif huruf besar-besaran, tiada penggantian dalam baris kedua terakhir, kerana dalam direktori kami mempunyai "SPb", bukan "SPb".
• Jika terdapat beberapa subrentetan untuk menggantikan sekali gus dalam data sumber (contohnya, dalam baris ke-7 anda perlu menggantikan kedua-dua "S-Pb" dan "Prospektus"), maka ini tidak menimbulkan sebarang masalah (tidak seperti menggantikan dengan formula daripada kaedah sebelumnya).
• Jika tiada apa-apa yang perlu diganti dalam teks sumber (baris ke-9), maka tiada ralat berlaku (tidak seperti, sekali lagi, daripada penggantian dengan formula).

Kelajuan permintaan sedemikian sangat, sangat baik. Sebagai contoh, untuk jadual data awal dengan saiz 5000 baris, pertanyaan ini dikemas kini dalam masa kurang dari satu saat (tanpa penimbal, secara kebetulan, kira-kira 3 saat!)

Bagaimana fungsi List.Accumulate berfungsi

Pada dasarnya, ini boleh menjadi penamat (untuk saya menulis, dan untuk anda membaca) artikel ini. Jika anda mahu bukan sahaja dapat, tetapi juga memahami cara ia berfungsi "di bawah tudung", maka anda perlu menyelam sedikit lebih dalam ke dalam lubang arnab dan berurusan dengan fungsi List.Accumulate, yang melakukan semua penggantian pukal bekerja untuk kita.

Sintaks untuk fungsi ini ialah:

=Senarai.Kumpul(senarai, benih, penumpuk)

di mana

• senarai ialah senarai yang unsur-unsurnya sedang kita ulangi. 
• benih - keadaan awal
• penumpuk – fungsi yang melakukan beberapa operasi (matematik, teks, dll.) pada elemen seterusnya senarai dan mengumpul hasil pemprosesan dalam pembolehubah khas.
  

Secara umum, sintaks untuk menulis fungsi dalam Power Query kelihatan seperti ini:

(argument1, argument2, … argumentN) => beberapa tindakan dengan hujah

Sebagai contoh, fungsi penjumlahan boleh diwakili sebagai:

(a, b) => a + b

Untuk List.Accumulate , fungsi accumulator ini mempunyai dua argumen yang diperlukan (ia boleh dinamakan apa-apa, tetapi nama biasa ialah adalah и semasa, seperti dalam bantuan rasmi untuk fungsi ini, di mana:

• adalah – pembolehubah di mana hasilnya terkumpul (nilai awalnya adalah yang disebutkan di atas benih)
  
• semasa – nilai berulang seterusnya daripada senarai senarai
  

Sebagai contoh, mari kita lihat langkah-langkah logik pembinaan berikut:

=Senarai.Kumpul({3, 2, 5}, 10, (keadaan, semasa) => keadaan + semasa)

Ini adalah terkumpul terbaru adalah nilainya ialah Senarai kami. Kumpul fungsi dan output sebagai hasilnya:

Jika anda berfantasi sedikit, kemudian menggunakan fungsi List.Accumulate, anda boleh mensimulasikan, sebagai contoh, fungsi Excel CONCATENATE (dalam Power Query, analognya dipanggil Teks.Gabungkan) menggunakan ungkapan:

Atau cari nilai maksimum (tiruan fungsi MAX Excel, yang dalam Power Query dipanggil Senarai. Maks):

Walau bagaimanapun, ciri utama List.Accumulate ialah keupayaan untuk memproses bukan sahaja teks ringkas atau senarai angka sebagai argumen, tetapi objek yang lebih kompleks – contohnya, senarai-dari-senarai atau senarai-dari-rekod (hello, Direktori!)

Mari kita lihat semula pembinaan yang melakukan penggantian dalam masalah kita:

Senaraikan. Kumpul(Direktori, [Alamat], (keadaan, semasa) => Teks. Ganti(keadaan, semasa[Cari], semasa[Ganti]) )

Apa sebenarnya yang berlaku di sini?

1. Sebagai nilai awal (benih) kami mengambil teks kekok pertama dari lajur [Alamat] meja kami: 199034, St. Petersburg, str. Beringa, d. 1
2. Kemudian List.Accumulate berulang ke atas elemen senarai satu demi satu – Buku panduan. Setiap elemen senarai ini ialah rekod yang terdiri daripada sepasang medan "Apa yang perlu dicari - Apa yang hendak digantikan" atau, dengan kata lain, baris seterusnya dalam direktori.
3. Fungsi penumpuk dimasukkan ke dalam pembolehubah adalah nilai awal (alamat pertama 199034, St. Petersburg, str. Beringa, d. 1) dan melaksanakan fungsi penumpuk padanya – operasi penggantian menggunakan fungsi M standard Teks. Ganti (bersamaan dengan fungsi SUBSTITUTE Excel). Sintaksnya ialah:

   Text.Replace(teks asal, apa yang kita cari, apa yang kita gantikan)

   dan di sini kita ada:

• adalah adalah alamat kotor kami, yang terletak di adalah (sampai ke sana dari benih)
  
• semasa[Cari] – nilai medan Untuk mencari daripada entri berulang senarai seterusnya Direktori, yang terletak pada pembolehubah semasa
• semasa[Ganti] – nilai medan Pengganti daripada entri berulang senarai seterusnya Direktoriberbaring dalam semasa

Oleh itu, untuk setiap alamat, kitaran penuh penghitungan semua baris dalam direktori dijalankan setiap kali, menggantikan teks daripada medan [Cari] dengan nilai daripada medan [Ganti].

Harap anda mendapat idea 🙂

• Gantikan secara pukal teks dalam senarai menggunakan formula
  
• Ungkapan Biasa (RegExp) dalam Power Query