Power Query'de List.Accumulate işleviyle toplu metin değiştirme

Referans listesine göre metnin formüllerle hızlı ve toplu olarak nasıl değiştirileceği - zaten çözdük. Şimdi bunu Power Query'de yapmaya çalışalım.

Sıklıkla olduğu gibi yapmak bu görev açıklamaktan çok daha kolay neden işe yarıyor ama ikisini birden yapmaya çalışalım 🙂

Böylece, sıradan aralıklardan klavye kısayoluyla oluşturulmuş iki "akıllı" dinamik tablomuz var. Ctrl+T veya takım Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir):

ilk masayı aradım Veri, ikinci tablo – rehberalan kullanma Tablo ismi (Tablo ismi) çıkıntı Inşaatçı (Tasarım).

Görev: tablodaki adresleri değiştirin Veri bir sütundaki tüm oluşumlar Bulmak El kitabı sütundan karşılık gelen doğru muadillerine Vekil. Hücrelerdeki metnin geri kalanına dokunulmadan kalmalıdır.

Adım 1. Dizini Power Query'ye yükleyin ve bir listeye dönüştürün

Aktif hücreyi referans tablosunda herhangi bir yere ayarladıktan sonra sekmeye tıklayın. Veri (Tarih)veya sekmesinde Güç Sorgu (Excel'in eski bir sürümüne sahipseniz ve Power Query'yi ayrı bir sekmede eklenti olarak yüklediyseniz) Tablodan/aralıktan (Tablodan/Aralıktan).

Referans tablosu, Power Query sorgu düzenleyicisine yüklenecektir:

Müdahale etmemek için otomatik olarak eklenen bir adım değiştirilmiş tip (Değiştirilen Tür) sağ panelde, uygulanan adımlar güvenli bir şekilde silinebilir, yalnızca adım bırakılır Kaynak (Kaynak):

Şimdi, daha fazla dönüşüm ve değiştirme yapmak için bu tabloyu bir listeye (liste) dönüştürmemiz gerekiyor.

lirik arasöz

• tablo birkaç satır ve sütundan oluşan iki boyutlu bir dizidir.
• Kayıt (Kayıt) – adları olan birkaç alan öğesinden oluşan tek boyutlu dizi dizisi, örneğin [İsim = “Maşa”, Cinsiyet = “f”, Yaş = 25]
  
• Liste – örneğin birkaç öğeden oluşan tek boyutlu bir dizi sütunu {1, 2, 3, 10, 42} or { "İnanc umut Aşk" }
  

Sorunumuzu çözmek için öncelikle türle ilgileneceğiz. Liste.

Buradaki hile, Power Query'deki liste öğelerinin yalnızca banal sayılar veya metin değil, aynı zamanda diğer listeler veya kayıtlar olabilmesidir. Kayıtlardan (kayıtlardan) oluşan o kadar zor bir liste (liste) içindedir ki dizinimizi çevirmemiz gerekir. Power Query sözdizimsel gösteriminde (girişler köşeli parantezler, küme parantezleri içindeki listeler) şöyle görünür:

{

    [ Bul = “Aziz. Petersburg”, Değiştir = “St. Petersburg” ] ,

    [ Bul = “Aziz. Petersburg”, Değiştir = “St. Petersburg” ] ,

    [ Bul = “Peter”, Değiştir = “St. Petersburg” ] ,

vb.

}

Böyle bir dönüştürme, Power Query'de yerleşik olarak bulunan M dilinin özel bir işlevi kullanılarak gerçekleştirilir. Tablo.Kayıtlara. Doğrudan formül çubuğuna uygulamak için bu işlevi oradaki adım koduna ekleyin. Kaynak.

It was:

Sonra:

Table.ToRecords fonksiyonunu ekledikten sonra tablomuzun görünümü değişecek – bir kayıt listesine dönüşecektir. Tek tek kayıtların içeriği, herhangi bir kelimenin yanındaki hücre arka planına tıklayarak görünüm bölmesinin alt kısmında görülebilir. Rekor (ama tek kelimeyle değil!)

Yukarıdakilere ek olarak, oluşturulan listemizi önbelleğe almak (arabelleğe almak) için bir vuruş daha eklemek mantıklıdır. Bu, Power Query'yi arama listemizi bir kez belleğe yüklemeye ve daha sonra değiştirmek için eriştiğimizde yeniden hesaplamamaya zorlar. Bunu yapmak için formülümüzü başka bir fonksiyona sarın – Liste.Tampon:

Bu tür önbelleğe alma, temizlenecek büyük miktarda ilk veri ile hızda çok fark edilir bir artış (birkaç kez!) sağlayacaktır.

Bu, el kitabının hazırlanmasını tamamlar.

tıklamak kalıyor Ana Sayfa – Kapat ve Yükle – Kapat ve Yükle… (Ana Sayfa — Kapat&Yükle — Kapat&Yükle..), bir seçenek seçin Sadece bir bağlantı oluşturun (Yalnızca bağlantı oluşturun) ve Excel'e dönün.

Adım 2. Veri tablosunun yüklenmesi

Burada her şey basmakalıp. Daha önce referans kitabında olduğu gibi, tabloda herhangi bir yere çıkıyoruz, sekmeye tıklayın Veri düğmesine tıklayın Tablodan/Aralıktan ve masamız Veri Power Query'ye girer. Otomatik olarak eklenen adım değiştirilmiş tip (Değiştirilen Tür) ayrıca şunları da kaldırabilirsiniz:

Bununla ilgili özel bir hazırlık eylemi yapılmasına gerek yoktur ve en önemli şeye geçiyoruz.

Adım 3. List.Accumulate işlevini kullanarak değiştirmeleri gerçekleştirin

Komutunu kullanarak veri tablomuza hesaplanmış bir sütun ekleyelim. Sütun Ekleme – Özel Sütun (Sütun ekle — Özel sütun): ve açılan pencereye eklenen sütunun adını girin (örneğin, düzeltilmiş adres) ve sihirli fonksiyonumuz Liste.Biriktir:

tıklamak kalıyor OK – ve yapılan değişikliklerin olduğu bir sütun alırız:

Dikkat:

• Power Query büyük/küçük harf duyarlı olduğundan, dizinde "SPb" değil "SPb" bulunduğundan sondan bir önceki satırda herhangi bir değiştirme olmadı.
• Kaynak verilerde aynı anda değiştirilecek birkaç alt dize varsa (örneğin, 7. satırda hem “S-Pb” hem de “İzahname”yi değiştirmeniz gerekir), bu herhangi bir sorun yaratmaz (aşağıdaki formüllerle değiştirmenin aksine). önceki yöntem).
• Kaynak metinde (9. satır) değiştirilecek bir şey yoksa, hata oluşmaz (yine formüllerle değiştirmeden farklı olarak).

Böyle bir isteğin hızı çok, çok iyi. Örneğin, 5000 satırlık bir ilk veri tablosu için, bu sorgu bir saniyeden daha kısa sürede güncellendi (bu arada, arabelleğe alma olmadan, yaklaşık 3 saniye!)

List.Accumulate işlevi nasıl çalışır?

Prensip olarak, bu makalenin sonu (benim yazmam ve sizin okumanız) olabilir. Sadece yapabilmek değil, aynı zamanda “kaputun altında” nasıl çalıştığını anlamak istiyorsanız, o zaman tavşan deliğine biraz daha derine dalmanız ve Liste ile uğraşmanız gerekecektir.Tüm toplu değiştirmeyi yapan Biriktirme işlevi bizim için çalış.

Bu işlevin sözdizimi şöyledir:

=Liste.Biriktir(liste, tohum, akümülatör)

nerede

• liste öğelerini yinelediğimiz listedir. 
• tohum - başlangıç ​​hali
• akümülatör – listenin bir sonraki öğesinde bazı işlemleri (matematiksel, metin vb.) gerçekleştiren ve işleme sonucunu özel bir değişkende toplayan bir işlev.
  

Genel olarak, Power Query'de işlev yazma sözdizimi şöyle görünür:

(argüman1, argüman2, … argümanN) => argümanlı bazı eylemler

Örneğin, toplama işlevi şu şekilde temsil edilebilir:

(a, b) => a + b

List.Accumulate için, bu akümülatör işlevinin iki gerekli argümanı vardır (bunlara herhangi bir ad verilebilir, ancak genel adlar şöyledir: belirtmek, bildirmek и akım, bu işlevin resmi yardımında olduğu gibi, burada:

• belirtmek, bildirmek - sonucun toplandığı bir değişken (başlangıç ​​değeri yukarıda belirtilen değerdir) tohum)
  
• akım – listeden bir sonraki yinelenen değer liste
  

Örneğin aşağıdaki yapının mantığının adımlarına bir göz atalım:

=Liste.Biriktir({3, 2, 5}, 10, (durum, akım) => durum + akım)

Bu en son birikmiş belirtmek, bildirmek değer bizim List.Accumulate işlevimizdir ve sonuç olarak çıktılar:

Biraz hayal kurarsanız, List.Accumulate işlevini kullanarak, örneğin Excel BİRLEŞTİR işlevini simüle edebilirsiniz (Power Query'de analogu denir Metin.Birleştir) ifadeyi kullanarak:

Hatta maksimum değeri arayın (Power Query'de çağrılan Excel'in MAX işlevinin taklidi). Liste.Maks):

Ancak, List.Accumulate'in ana özelliği, argüman olarak yalnızca basit metin veya sayısal listeleri değil, aynı zamanda daha karmaşık nesneleri de işleme yeteneğidir - örneğin, listelerden gelen listeler veya kayıtlardan gelen listeler (merhaba, Dizin!)

Problemimizde yer değiştirmeyi gerçekleştiren yapıya tekrar bakalım:

Liste.Biriktir(rehber, [Adres], (durum,geçerli) => Metin.Değiştir(durum, geçerli[Bul], geçerli[Değiştir]) )

Burada gerçekten neler oluyor?

1. Başlangıç ​​değeri olarak (tohum) sütundan ilk sakar metni alıyoruz [Adres] bizim masamız: 199034, St.Petersburg, str. Beringa, d. 1
2. Ardından List.Accumulate, listenin öğelerini birer birer yineler – El kitabı. Bu listenin her bir öğesi, “Ne bulmalı – Neyle değiştirmeli” alanından veya başka bir deyişle dizindeki bir sonraki satırdan oluşan bir kayıttır.
3. Akümülatör işlevi bir değişkene koyar belirtmek, bildirmek başlangıç ​​değeri (ilk adres 199034, St.Petersburg, str. Beringa, d. 1) ve üzerinde bir akümülatör işlevi gerçekleştirir - standart M işlevini kullanarak değiştirme işlemi Metin.Değiştir (Excel'in İKAME işlevine benzer). Sözdizimi:

   Text.Replace( orijinal metin, ne arıyoruz, ne ile değiştiriyoruz)

   ve işte elimizde:

• belirtmek, bildirmek bizim kirli adresimiz, içinde yatan belirtmek, bildirmek (oradan gelen tohum)
  
• mevcut[Ara] - alan değeri Bulmak listenin bir sonraki yinelenen girişinden rehber, değişkende yatan akım
• geçerli[Değiştir] - alan değeri Vekil listenin bir sonraki yinelenen girişinden rehberuzanmak akım

Bu nedenle, her adres için, her seferinde [Bul] alanındaki metni [Değiştir] alanındaki değerle değiştirerek, dizindeki tüm satırların tam bir numaralandırma döngüsü çalıştırılır.

Umarım fikir edinmişsinizdir 🙂

• Formülleri kullanarak bir listedeki metni toplu olarak değiştirin
  
• Power Query'de Normal İfadeler (RegExp)