İleri Ders 9: Power Query'ye Giriş

Power Query ne zaman işe yarar?

Power Query, veriyi Excel'e alırken temizleyen ve dönüştüren, yaptığınız her işlemi adım olarak kaydeden bir araçtır. Her ay bir muhasebe programından aynı biçimde dışa aktarılan bir dosyayı düşünün: fazladan boşluklar, gereksiz sütunlar, metin olarak gelmiş tutarlar. Bu temizliği formüllerle her ay yeniden yapmak yerine Power Query'de bir kez tanımlarsınız, sonraki aylarda Yenile demeniz yeterli olur.

Formüller sonuçları hücrelerde hesaplar ve kaynak veriyi yerinde bırakır. Power Query ise kaynak veriyi değiştirmeden yeni, temiz bir tablo üretir. Microsoft 365, Excel 2021, 2019 ve 2016'da Veri sekmesinde yerleşik olarak gelir.

Bir tabloyu sorguya almak

Başlamak için veri aralığınızın içinde bir hücre seçin ve Veri sekmesindeki Al & Dönüştür grubundan Tablodan/Aralıktan komutunu tıklayın. Aralık henüz Excel tablosu değilse Excel önce tablo oluşturmanızı ister. Ardından Power Query Düzenleyicisi açılır ve verinin önizlemesini gösterir.

Sorgu Ayarları bölmesinin üstündeki ad kutusundan sorgunun adını değiştirebilirsiniz. "Tablo1" yerine "Siparisler_Temiz" gibi bir ad, dosyada birden fazla sorgu olduğunda hangisinin ne yaptığını gösterir.

Düzenleyicinin sağındaki Sorgu Ayarları bölmesi, uygulanan adımları sırayla listeler. İlk adımlar genellikle Kaynak ve Tür değiştirildi'dir. Bir adımı tıkladığınızda veri o adımdaki hâliyle görünür. Yanlış bir adımı adın yanındaki çarpı işaretiyle silebilirsiniz.

Temizleme adımları

En sık kullanılan temizlik işlemleri sütun başlığına sağ tıkladığınızda ya da Dönüştür sekmesinde bulunur. Şehir adlarının başında ve sonunda boşluk varsa metin biçimlendirme komutlarındaki kırpma işlemi bu boşlukları atar. Formüllerle aynı iş KIRP fonksiyonuyla yapılır: =KIRP(A2) " Ankara" değerini "Ankara"ya çevirir.

Veri türü belirlemek de önemli bir adımdır. Tutar sütunu metin olarak geldiyse sütun başlığındaki tür simgesine tıklayıp sayı türlerinden birini seçersiniz. Bu adım atlanırsa sonraki toplama işlemleri hata verir ya da sıfır döndürür.

Gereksiz sütunları kaldırmak, boş satırları filtrelemek ve sütunları bölmek de aynı mantıkla çalışır. Her işlem Sorgu Ayarları'na yeni bir adım olarak eklenir. Kaynak dosya değiştiğinde bu adımlar aynı sırayla yeniden uygulanır.

Gruplandırma ölçütü ile özet

Gruplandırma ölçütü, satırları bir sütuna göre toplayıp özet tablo üretir. Giriş sekmesindeki Gruplandırma ölçütü komutunu açın, gruplandırma sütunu olarak Şehir'i seçin. Yeni sütun adına "Toplam tutar" yazın, işlem olarak Topla'yı ve sütun olarak Tutar'ı seçin.

Altı satırlık sipariş verisinde sonuç üç satıra iner: Ankara 2.300, İzmir 1.700 ve Bursa 1.500 TL. İşlem olarak Satırları Say seçerseniz her şehrin sipariş sayısını alırsınız; Ankara için 3. Bu sonuçlar ÇOKETOPLA ve EĞERSAY ile hesaplanan değerlerle aynıdır, aradaki fark Power Query'nin bunu kaynak tablodan ayrı, yenilenebilir bir tabloda yapmasıdır.

Power Query mi, formül mü?

İkisi aynı soruya farklı açılardan cevap verir. Veri her ay aynı biçimde geliyorsa, birkaç bin satırı aşıyorsa ya da birden çok dosyanın birleştirilmesi gerekiyorsa Power Query daha uygundur. Adımlar bir kez kurulur ve her yenilemede aynı sırayla çalışır.

Tek seferlik bir düzeltme ya da hücre düzeyinde anlık hesap gerekiyorsa formül daha hızlıdır. KIRP ile birkaç hücreyi temizlemek için sorgu kurmaya gerek yoktur. Formül sonucu ayrıca kaynak değişir değişmez güncellenir, sorgu ise yenilenmeyi bekler.

Pratikte ikisi birlikte kullanılır. Power Query ham veriyi temizleyip bir tabloya yükler, formüller ve pivot tablolar da bu temiz tablo üzerinde çalışır. Bu ayrım, hesap formüllerinin kirli veriyle uğraşmasını önler.

Yükleme ve yenileme

Düzenleyicide işiniz bitince Giriş sekmesindeki Kapat & Yükle komutu sonucu yeni bir sayfaya Excel tablosu olarak yükler. Kapat & Yükle menüsündeki diğer seçenekle sonucu var olan bir sayfaya ya da yalnızca bağlantı olarak almayı seçebilirsiniz.

Kaynak veriye yeni satır eklediğinizde sonuç tablosu kendiliğinden değişmez. Veri sekmesindeki Tümünü Yenile ya da sonuç tablosuna sağ tıklayıp Yenile, sorguyu baştan çalıştırır. İlk birkaç yenilemede sonucu bir formülle karşılaştırmak, adımların beklediğiniz gibi çalıştığını gösterir.

Sıradaki adım

Alıştırmada Power Query'nin Gruplandırma ölçütü adımıyla üreteceği şehir toplamlarını ÇOKETOPLA ile hesaplayacaksınız. Ardından aynı veriyi Tablodan/Aralıktan ile sorguya alıp Gruplandırma ölçütü uygulayın ve iki sonucu yan yana koyun.

=KIRP(A2)

=TRIM(A2)

=ÇOKETOPLA(B2:B7;A2:A7;"Ankara")

=SUMIFS(B2:B7,A2:A7,"Ankara")

=EĞERSAY(A2:A7;"Ankara")

=COUNTIF(A2:A7,"Ankara")