İçerik
Normal ifadelere en azından biraz aşina iseniz, bunların reklamını yapmanıza gerek yoktur. Konuya tam olarak hakim değilseniz, düzenli ifadeler (Normal İfadeler = RegExp = “regexps” = “regulars”), metinde özel karakterler ve kurallar kullanılarak gerekli alt dizelerin arandığı, çıkarıldığı bir dildir. veya başka bir metinle değiştirilmiştir. Bu çok güçlü ve güzel bir araçtır, metinle çalışmanın diğer tüm yollarından çok daha üstündür.
Basit makrolar kullanarak Excel'e düzenli ifade desteğini nasıl ekleyebileceğinizi zaten ayrıntılı olarak ve hayattan bir sürü örnekle anlattım - bu makaleyi okumadıysanız, devam etmeden önce okumanızı şiddetle tavsiye ederim. Bir sürü yeni şey keşfedeceksiniz, garanti ederim 🙂
Ancak, soru açık kalıyor: Power Query'de normal ifadeleri kullanma yeteneği nasıl eklenir? Power Query, elbette, kendi başına iyidir ve metinle çok şey yapabilir (kesme, yapıştırma, temizleme, vb.), ancak normal ifadelerin gücüyle onu geçebilseydiniz, bu sadece bir bomba olurdu.
Ne yazık ki, Power Query'de RegExps ile çalışmak için yerleşik işlevler yoktur ve resmi Microsoft yardım ve teknik desteği bu soruyu olumsuz yanıtlar. Ancak, bu sınırlamayı aşmanın bir yolu var 🙂
Yöntemin özü
Ana fikri rezil etmek basittir.
Yerleşik Power Query yetenekleri listesinde bir işlev vardır. İnternet sayfası. Bu işlevin resmi Microsoft yardım sitesindeki açıklaması son derece kısadır:
Tercüme edildiğinde, bu şöyle olur: "Etiketler kaldırıldıktan sonra tüm belgenin ve gövdesinin bir temsilinin yanı sıra bileşen yapılarına ayrılmış HTML belgesinin içeriğini döndürür." Bu kadar açıklama, açıkçası.
Genellikle bu işlev, web'den veri içe aktarılırken kullanılır ve örneğin sekmede seçtiğimizde otomatik olarak değiştirilir. Veri Komuta İnternetten (Veri — Web'den). Fonksiyona argüman olarak bir web sayfası veriyoruz ve daha önce tüm etiketleri temizledikten sonra içeriğini bize tablolar şeklinde geri veriyor.
Yardımın söylemediği şey, HTML biçimlendirme diline ek olarak işlev İnternet sayfası JavaScript komut dosyalarını destekler, artık İnternet'teki web sitelerinde her yerde bulunur. JavaScript ise her zaman normal ifadelerle çalışabilmiştir ve RegExps için yerleşik işlevlere sahiptir! Bu nedenle, Power Query'de normal ifadeler uygulamak için, Web.Page işlevlerini Power Query için tüm işi yapacak küçük bir JavaScript programına bir argüman olarak beslememiz gerekecek.
Saf JavaScript'te nasıl göründüğü
İnternette JavaScript'te normal ifadelerle çalışmaya ilişkin çok sayıda ayrıntılı eğitim vardır (örneğin, bir, iki).
Kısaca ve basitleştirilmiş olarak, JavaScript kodu şöyle görünecektir:
İşte:
- var str = '123 ve 789 faturalarını sosis için öde'; - bir değişken oluşturun str ve analiz edeceğimiz kaynak metni ona atayın.
- var desen = /d+/gi; – normal bir ifade oluşturun ve onu bir değişkene koyun model.
İfade eğik çizgi (/) ile başlar.
Buradaki ifadenin kendisi, örneğin, d+ herhangi bir rakam dizisi anlamına gelir.
İfadeden sonraki kesir boyunca ek arama parametreleri (değiştiriciler) vardır - bunlar herhangi bir sırada belirtilebilir:
- g – genel arama anlamına gelir, yani bir eşleşme bulduktan sonra durmamalı, metnin sonuna kadar aramaya devam etmelisiniz. Bu değiştirici ayarlanmazsa, komut dosyamız yalnızca ilk eşleşmeyi döndürür (123)
- i - harf durumuna bakılmaksızın arama yapın
- m – çok satırlı arama (kaynak metin birkaç satıra bölündüğünde kullanılır)
- var sonuç = str.match(pattern).join(';'); – kaynak metinde bir arama yapın (str) verilen normal ifadeyle (model) ve sonuçları bir değişkene koyun sonuç, komutu kullanarak bunları noktalı virgülle birleştirerek kaydol
- belge.write(sonuç); – sonuç değişkeninin içeriğini göster
Ayrıca JavaScript'teki metin dizelerinin (normal ifadeler hariç) Power Query veya VBA'da olduğu gibi tırnak işaretleri içinde değil, kesme işaretleri içinde olduğunu unutmayın.
Çıktıda, bu komut dosyası bize kaynak metinde bulunan tüm sayıları sonuç olarak verecektir:
123, 789
JavaScript kısa kursu sona erdi, hepinize teşekkürler. Umarım mantığı anlarsın 🙂
Geriye bu yapıyı Power Query'ye aktarmak kalıyor.
Power Query'de Normal İfadeye Göre Metin İşlevini Arama ve Çıkarma
Aşağıdakileri yapıyoruz:
1. Excel'i açın ve sekmede yeni bir boş Power Query oluşturun Veri – Veri al / İstek oluştur – Diğer kaynaklardan – Boş istek (Veri — Veri al / Yeni sorgu — Diğer kaynaklardan — Boş sorgu). Excel 2010-2013'ün eski bir sürümüne ve Power Query'ye sahipseniz, yerleşik olmayan ancak ayrı bir eklenti olarak yüklenmişse, tüm bunlar sekmede olacaktır. Güç SorguVe yok Veri.
2. Açılan sorgu düzenleyicinin boş penceresinde, sağ panelde hemen gelecekteki işlevimizin adını girin (örneğin, fxRegExpÖzet)
3. hadi sekmeye gidelim Görünüm – Gelişmiş Düzenleyici (Görünüm — Gelişmiş Düzenleyici), boş isteğin tüm M kodunu sileriz ve süper işlevimizin kodunu buraya yapıştırırız:
Ellerine dikkat et:
İlk satırda, fonksiyonumuzun üç metin argümanına sahip olacağını söylüyoruz: txt – analiz edilen orijinal metin, regex – düzenli ifade kalıbı, sınırlamak — sonuçları görüntülemek için sınırlayıcı karakter.
Sonra fonksiyonu çağırıyoruz İnternet sayfası, argümanında yukarıda açıklanan JavaScript kodunu oluşturur. Değişken argümanlarımızı koda yapıştırır ve değiştiririz.
Parça:
[Veri]{0}[Çocuklar]{0}[Çocuklar]{1}[Metin]{0}
… ihtiyacımız olan sonuçlarla masaya “düşmek” için gereklidir. Mesele şu ki, fonksiyon İnternet sayfası sonuç olarak, bir web sayfasının yapısını tekrar eden birkaç iç içe tablo üretir. Bu M kodu parçası olmadan, fonksiyonumuz şunu verirdi:
… ve kelimeyi birkaç kez tıklamamız gerekecekti tablo, art arda sütunlardaki alt iç içe tablolara "düşme" Çocuk:
Tüm bu alıntılar yerine, hangi tablo ve sütunun iç içe olduğunu hemen fonksiyonumuzun kodunda belirtiyoruz (Metin) ihtiyacımız var.
İşte, aslında, tüm sırlar. Düğmeye basmak için kalır Bitiş pencerede gelişmiş düzenleyici, kodumuzu eklediğimiz yer ve en lezzetli olana geçebilirsiniz – fonksiyonumuzu iş başında deneyin.
İşte birkaç tohum örneği.
Örnek 1. Ödeme açıklamasından hesap numarası ve tarihi alma
Ödenen faturaların sayılarını ve tarihlerini ayrı sütunlara çekmeniz gereken, ödemelerin açıklamasını (amacını) içeren bir banka ekstremiz var:
Tabloyu standart yoldan Power Query'ye yüklüyoruz Veriler – Tablodan/Aralıktan (Veri - T'denyetenekli/Rmelek).
Ardından fonksiyonumuzla hesaplanmış bir sütun ekliyoruz. Sütun Ekle - Özel İşlevi Çağır (Sütun Ekle — Özel İşlevi Çağır) ve argümanlarını girin:
Normal bir ifade olarak (argüman regex) kullandığımız şablon:
(g{3,5}|d{2}.d{2}.d{4})
… insan diline çevrilmiş anlamı:
3 ila 5 basamaklı sayılar (hesap numaraları)
or
“2 bitlik sayı – nokta – 2 bitlik sayı – nokta – 4 bitlik sayı” biçimindeki parçalar, yani GG.AA.YYYY biçimindeki tarihler.
Sınırlayıcı karakter olarak (argüman sınırlamak) noktalı virgül girin.
Tıkladıktan sonra OK sihirli fonksiyonumuz tüm ilk verileri normal ifademize göre analiz eder ve bulunan faturaların sayıları ve tarihleri ile bizim için bir sütun oluşturur:
Komutu kullanarak noktalı virgülle ayırmak için kalır Ana Sayfa — Sütunu Böl — Sınırlayıcıya Göre (Ana Sayfa — Sütunu böl — Sınırlayıcıya göre) ve istediğimizi elde ederiz:
Güzellik!
Örnek 2: Metinden e-posta adreslerini çıkarın
İlk veri olarak aşağıdaki tabloya sahip olduğumuzu varsayalım:
… orada bulunan e-posta adreslerini çıkarmamız gereken yerden (açıklık için metinde onları kırmızı ile vurguladım).
Önceki örnekte olduğu gibi, tabloyu Power Query'ye standart yolla şu şekilde yüklüyoruz: Veriler – Tablodan/Aralıktan (Veri - T'denyetenekli/Rmelek).
Ardından fonksiyonumuzla hesaplanmış bir sütun ekliyoruz. Sütun Ekle - Özel İşlevi Çağır (Sütun Ekle — Özel İşlevi Çağır) ve argümanlarını girin:
E-posta adreslerini ayrıştırmak daha zor bir iştir ve bunu çözmek için değişen derecelerde kabusların bir sürü düzenli ifadesi vardır. Basit seçeneklerden birini kullandım - ideal değil, ancak çoğu durumda oldukça işe yarıyor:
[w|.|-]*@w*.[w|.]*
ayırıcı olarak (sınırlamak) noktalı virgül ve boşluk girebilirsiniz.
Tıklayın OK ve orijinal “püresi” metninden çıkarılan e-posta adreslerini içeren bir sütun alıyoruz:
Sihirli!
PS
Söylediği gibi: "Daha da iyi hale getirilemeyecek kadar iyi bir şey yoktur." Power Query kendi başına harikadır ve normal ifadelerle birleştirildiğinde, herhangi bir metin verisini işlemede bize tamamen gerçekçi olmayan güç ve esneklik sağlar. Umarım Microsoft bir gün Power Query ve Power BI güncellemelerine RegExp desteği ekler ve yukarıdaki tüm tef dansları geçmişte kalır. Şimdilik, evet.
Ayrıca https://regexr.com/ sitesinde - doğrudan çevrimiçi düzenleyicide normal ifadelerle oynamanın uygun olduğunu da eklemek istiyorum. orada, bölümde Topluluk Kalıpları Tüm durumlar için çok sayıda hazır mevsim vardır. Deney – normal ifadelerin tüm gücü artık Power Query'de hizmetinizde!
- Normal ifadeler (RegExp) nedir ve bunların Excel'de nasıl kullanılacağı
- Power Query'de bulanık metin araması
- Power Query kullanarak farklı dosyalardan tabloları birleştirme