Power Query ile Klasördeki Dosyalar Nasıl Birleştirilir?

Bu yöntem hangi işi çözer?

Her ay ayrı bir dosya olarak gelen satış, gider ya da stok raporlarını tek tabloda toplamak için Power Query'nin klasör bağlayıcısı kullanılır. Ocak, şubat ve mart dosyalarını tek tek açıp kopyalamak yerine klasörü bir kez bağlarsınız. Nisan dosyası aynı klasöre kaydedildiğinde Yenile'ye basmanız yeterli olur.

Yöntemin bir şartı vardır: dosyaların aynı yapıda olması gerekir. Microsoft'un destek sayfasına göre dosyalar aynı sütun başlıklarını, veri türlerini ve sütun sayısını taşımalıdır. Eşleştirme sütun adlarına göre yapıldığı için sütunların sırası farklı olabilir.

Klasörü hazırlamak

Birleştirilecek dosyaları yalnız bu iş için ayrılmış bir klasöre koyun, örneğin Belgeler altında Satış Raporları adlı bir klasöre. Klasördeki her dosya ve seçilen alt klasörler birleştirmeye dahil edilir. Yanlışlıkla bırakılmış bir not dosyası ya da geçici kopya hata verir veya tabloya beklenmedik satırlar ekler.

Dosya adlarını dönemi anlatacak şekilde verin: Ocak.xlsx, Şubat.xlsx gibi. Power Query birleştirilmiş tabloya her satırın hangi dosyadan geldiğini gösteren bir sütun ekler; anlamlı dosya adları bu sütunu doğrudan bir dönem bilgisine dönüştürür.

Klasörden veri almak

Veri sekmesinde Veri Al menüsünü açın, Dosyadan altında Klasörden seçeneğini seçin. Gözat penceresinde klasörünüzü bulup açın. Excel klasördeki dosyaları ad, uzantı ve değiştirilme tarihi gibi bilgilerle listeleyen bir pencere gösterir.

Pencerenin altında üç seçenek bulunur: Birleştir, Birleştir ve Yükle, Birleştir ve Dönüştür. Sonucu yüklemeden önce kontrol etmek ve düzenlemek için Birleştir ve Dönüştür'ü seçin. Bu seçenek önce birleştirme ayarlarını, ardından Power Query Düzenleyicisi'ni açar.

Dosyaları Birleştir penceresi

Dosyaları Birleştir penceresinde Örnek Dosya listesi bulunur. Power Query, seçtiğiniz örnek dosyanın yapısını bütün dosyalara uygular; bu yüzden düzgün ve eksiksiz bir dosyayı örnek seçin. Excel dosyalarında hangi sayfanın ya da tablonun alınacağını da bu pencerede seçersiniz.

Hatalı dosyaları atla seçeneği, açılamayan ya da yapısı farklı dosyaları birleştirmenin dışında bırakır. Tamam'a bastığınızda düzenleyicide bütün dosyaların satırlarını içeren tek bir tablo görünür. Sorgular listesinde ayrıca örnek dosya ve yardımcı fonksiyon sorguları oluşur; bunları silmeyin, birleştirme bunlara dayanır.

Dosya adı sütununu kullanmak

Birleştirilmiş tablonun ilk sütunu, her satırın geldiği dosyanın adını taşır; İngilizce arayüzde bu sütunun adı Source.Name'dir. Değerler Ocak.xlsx gibi uzantılı görünür. Sütunu seçip Değerleri Değiştir ile .xlsx kısmını boş değerle değiştirdiğinizde temiz bir Ay sütunu elde edersiniz ve pivot tabloda dönem kırılımı olarak kullanabilirsiniz.

Birleştirmenin doğru çalıştığını, dosya bazında bir toplamı kontrol ederek görebilirsiniz. Ocak dosyasındaki satışların toplamı birleşik tabloda da aynı olmalıdır.

=ETOPLA(A2:A7;"Ocak.xlsx";C2:C7)

=SUMIF(A2:A7,"Ocak.xlsx",C2:C7)

Genel toplamı da kaynak dosyaların toplamlarıyla karşılaştırın. Üç dosyanın toplamı 6190 TL ise birleşik tablonun tutar sütunu da 6190 vermelidir.

=TOPLA(C2:C7)

=SUM(C2:C7)

Yeni dosyaları eklemek

Nisan raporunu aynı klasöre kaydettiğinizde sorguyu açmanıza gerek yoktur. Veri sekmesindeki Tümünü Yenile ya da sorgunun sağ tık menüsündeki Yenile, klasörü yeniden okur ve yeni dosyayı tabloya katar. Bir dosyayı klasörden çıkarırsanız satırları da yenileme sonrası tablodan çıkar.

Sık karşılaşılan sorunlar

Bir dosyada sütun adı farklı yazılmışsa, örneğin Tutar yerine Tutar (TL), o dosyanın tutarları ayrı bir sütuna düşer ya da boş görünür. Kaynak dosyaları aynı şablondan üretmek bu sorunu baştan önler. Klasörde istenmeyen dosyalar varsa düzenleyicide uzantı ya da klasör yolu sütununa göre filtreleyerek yalnız .xlsx dosyalarını bırakabilirsiniz.

Klasörün yeri değiştiğinde yenileme hata verir. Sorgunun Kaynak adımını açıp yeni klasör yolunu seçmek sorunu çözer.

Örnek dosyada yaptığınız dönüştürmeler, örneğin başlığın üstündeki iki açıklama satırını silmek, yardımcı sorgu aracılığıyla bütün dosyalara uygulanır. Bu yüzden bir dosyaya özgü düzeltmeyi birleşik tabloda değil, kaynak dosyanın kendisinde yapmak daha güvenlidir.

CSV dosyalarında dikkat edilecekler

Klasördeki dosyalar CSV ise Power Query her dosyayı ayraç ve karakter kodlamasına göre okur. Türkçe karakterler bozuk görünüyorsa Dosyaları Birleştir penceresindeki dosya kaynağı ayarından UTF-8 kodlamasını seçmeyi deneyin. Ondalık ayracı virgül olan dosyalarda tutar sütununun türünü değiştirirken yerel ayar seçeneğiyle Türkçe'yi belirtmek, 1.250,50 gibi değerlerin doğru sayıya dönüşmesini sağlar.

Bir sonraki adım

Son üç ayın raporlarını ayrı bir klasöre koyun ve Birleştir ve Dönüştür ile tek tabloya alın. Dosya adı sütununu Ay sütununa çevirip sonucu pivot tabloyla aylara göre özetleyin.