Excel Power Query ile veri nasıl temizlenir ve birleştirilir?

Power Query nedir, ne zaman gerekir?

Power Query, Excel'in içinde yer alan veri alma ve dönüştürme aracıdır. Dosyalardan, klasörlerden, veritabanlarından ya da web kaynaklarından veriyi çeker, bir dizi adımla temizler ve sonucu çalışma sayfasına ya da veri modeline yükler. Asıl gücü, uyguladığınız her adımı kaydetmesidir: kaynak veri yenilendiğinde aynı adımlar yeniden otomatik çalışır.

Elle yapılan kopyala-yapıştır, bul-değiştir ve formül sürüklemeyle yürüyen haftalık raporlama işleri Power Query için tipik adaydır. Aynı temizlik işlemini her dönem baştan yapıyorsanız, bu işi bir sorguya çevirmek zaman ve hata kazandırır.

Adım adım: veriyi alma ve temizleme

• Veriyi alın: Veri sekmesinden dosya, klasör, veritabanı ya da web kaynağını seçin. Bir klasördeki aynı yapılı birçok dosyayı tek seferde birleştirmek mümkündür.
• Başlıkları ve veri türlerini düzeltin: İlk satırı başlık yapın, her sütunun türünü (metin, sayı, tarih) doğru atayın. Yanlış tür, sonraki adımlarda hatalara yol açar.
• Gereksiz satır ve sütunları kaldırın: Boş satırlar, üst bilgi satırları, tekrar eden kayıtlar ve kullanılmayan sütunlar silinir.
• Metni temizleyin: Baştaki ve sondaki boşlukları kırpın, büyük-küçük harf tutarsızlıklarını düzeltin, sütunları ayırıcıya göre bölün.
• Eksik değerleri yönetin: Boş hücreleri üstteki değerle doldurun, belirli bir değerle değiştirin ya da o satırları çıkarın.
• Hataları ayıklayın: Dönüştürme sırasında hata üreten satırları ayrı bir sorguda inceleyin.

Her adım sağ taraftaki Uygulanan Adımlar listesinde görünür. Hatalı adımı silmek, sırayı değiştirmek ya da bir önceki hâle dönmek kolaydır.

Tabloları birleştirme: ekleme ve eşleştirme

Power Query'de birleştirmenin iki ana biçimi vardır ve karıştırılmamalıdır. Ekleme (append), aynı sütun yapısına sahip tabloları alt alta yığar: örneğin her ay için ayrı dosyada tutulan satış kayıtlarını tek tabloda toplamak. Birleştirme (merge) ise iki tabloyu ortak bir anahtar sütun üzerinden yan yana getirir: örneğin sipariş tablosuna müşteri tablosundan şehir bilgisini eklemek.

Birleştirmede eşleşme türü sonucu belirler. Yalnızca iki tabloda da bulunan satırlar mı, soldaki tablonun tüm satırları mı istiyorsunuz? Anahtar sütunlarda boşluk ya da büyük-küçük harf farkı varsa eşleşme sessizce kaybolur, bu yüzden anahtar sütunları önce temizleyin. Eşleştirmeden sonra satır sayısının beklediğiniz gibi olup olmadığını kontrol etmek iyi bir alışkanlıktır.

Yenilenebilir sorgular ve Power Pivot'a geçiş

Sorgunun değeri, tek tıkla yenilenebilmesindedir. Kaynak dosya güncellendiğinde yenile komutu tüm adımları yeniden çalıştırır. Bu yüzden sorgu adımlarında sabit hücre adresine ya da elle yazılmış değerlere dayanmaktan kaçının, kaynak yapısındaki küçük değişikliklere dayanıklı adımlar kurun.

Temizlenen tablolar çalışma sayfasına yüklenebileceği gibi veri modeline de yüklenebilir. Veri modelinde Power Pivot ile tablolar arasında ilişkiler kurulur, DAX adı verilen formül dili ile ölçüler tanımlanır ve pivot tablolarda birden fazla tablodan tek raporda özet alınır. Böylece Power Query veriyi hazırlar, Power Pivot onu analiz edilebilir bir modele dönüştürür.

Somut örnek: şubelerden gelen aylık raporlar

Örneğin her şube ayın sonunda aynı yapıda bir Excel dosyası gönderiyor olsun. Dosyalar tek klasörde toplanır, Power Query ile klasör kaynağı olarak okunur ve tek tabloya birleştirilir. Ardından başlıklar ve türler düzeltilir, boş satırlar çıkarılır, şube adı dosya adından çıkarılarak yeni bir sütuna yazılır.

Sonraki ay klasöre yeni dosyalar eklendiğinde sorguyu yenilemek yeterlidir. Daha önce günlerce süren birleştirme ve temizleme işi bir tıklamaya iner; hesaplamaların aynı kurala göre yapılması da tutarlılığı artırır.

Veri kaynağı değiştiğinde sorgunun dayanıklılığı

Power Query ile yaşanan en büyük hayal kırıklığı, kaynağın biçimi değiştiğinde sorgunun hata vermesidir. Bunun en yaygın nedenleri sütun adının değişmesi, yeni bir sütunun eklenmesi, başlık satırının kayması ya da sayfa adının farklı gelmesidir. Dayanıklı sorgu kurmanın yolu, bu riskleri baştan düşünmektir.

Sütunları isimle değil mümkünse yapıya göre seçin; "tüm sütunları kaldır, şunları tut" yerine hangi sütunların gerektiğini belirleyin ve gerisini tutun. Sayfa adı yerine tablo adını kaynak olarak kullanın, çünkü tablo adı sayfa düzeni değişse de sabit kalır. Dosya yolunu parametreye çevirin ki klasör taşındığında tek bir yerde değiştirebilesiniz.

Sorguyu devrettiğinizde yanına kısa bir not ekleyin: kaynağın nerede durduğu, hangi sütunların beklendiği, yenilemenin nasıl yapılacağı. Yenileme hata verirse sorgudaki hatalı adımın adı genellikle sorunun nerede olduğunu gösterir; önce o adımı ve bir önceki adımın çıktısını inceleyin.

Öğrenme yolu ve sık yapılan hatalar

Önce arayüze alışın: veri alma, sütun dönüştürme ve adımları düzenleme. Sonra birleştirme ve ekleme, koşullu sütun, sütunları ters çevirme (unpivot) ve gruplama gibi dönüşümlere geçin. İleri aşamada M dilinde küçük düzenlemeler, parametreli sorgular ve özel fonksiyonlar yer alır.

• Veri türünü atlamak: Tarih metin olarak kalırsa sıralama ve filtreleme yanlış çalışır.
• Ham veriyi ezmek: Kaynak dosyada elle değişiklik yapmak, sorgunun amacını bozar; temizliği sorguda yapın.
• Tüm adımları tek sorguda yığmak: Karmaşıklaşınca takip zorlaşır, ara sorguları ayırın.
• Yenilemeyi test etmemek: Yeni verinin eklendiği senaryoda sorguyu çalıştırarak deneyin.

Bu beceriyi Mindset programlarında nerede çalışırsınız?

Mindset kataloğundaki "Excel Power Query ve Power Pivot" eğitiminin modülleri Power Query temelleri ve arayüze giriş, veri dönüştürme ve temizleme, ileri Power Query teknikleri, veri modeline giriş ve Power Pivot, Power Pivot için DAX temelleri, Power Pivot ile pivot tablo entegrasyonu ve kurumsal iş akışı ile otomasyonu içerir.