Klasördeki Excel ve CSV Dosyalarını Tek Tabloda Birleştirmek

Klasörden birleştirme nasıl çalışır?

Folder bağlayıcısı bir klasörün içindeki tüm dosyaları, alt klasörlerdekiler dahil, bir tablo olarak getirir. Bu tabloda her satır bir dosyadır; dosya adı, uzantı, klasör yolu ve değişiklik tarihi gibi alanların yanında Content adlı bir sütun bulunur. Content sütunu, dosyanın ham içeriğini (binary) taşır ve henüz okunmamıştır.

Birleştirme (Combine) komutu bu ham içerikleri okumayı otomatikleştirir. Power Query listedeki ilk dosyayı örnek alır, uzantısına bakarak uygun bağlayıcıyı (CSV, Excel, JSON ya da XML) seçer, örnek dosya için gerekli çıkarma adımlarını kurar ve aynı adımları bir fonksiyona dönüştürür. Bu fonksiyon, klasördeki her dosyaya uygulanır ve çıkan tablolar alt alta eklenir.

Bu mantığın sonucu şudur: örnek dosyayı bir kez doğru hazırlarsanız tüm dosyalar aynı işlemden geçer. Bunun bedeli de aynı mantıktan gelir: örnek Excel dosyasında "Tablo" adında bir nesne seçtiyseniz, Power Query diğer tüm dosyalarda da o adla bir nesne arar.

Adım adım klasörden dosya birleştirme

Aylık satış dosyalarının tek bir klasörde durduğunu düşünelim.

• Veri al menüsünden Folder seçeneğini seçin, klasör yolunu yazın ya da göz at ile bulun, Tamam'a basın.
• Önizlemede klasör ve alt klasörlerdeki tüm dosyalar listelenir. Tümünü birleştireceğinizden eminseniz "Combine & Transform Data" seçeneğini seçin. Doğrudan yükleme için "Combine & Load", dosya listesini olduğu gibi görmek için "Transform Data" vardır.
• Birleştirme penceresinde örnek dosyayı seçin. Varsayılan ilk dosyadır; farklı bir dosya daha temsili ise açılır listeden onu seçin. Hatalı dosyaları dışarıda bırakmak için "Skip files with errors" kutusunu işaretleyebilirsiniz.
• Gerekirse CSV için ayırıcı ve kodlama, Excel için sayfa ya da tablo seçimini yapın.
• Power Query Düzenleyicisi açılır. Soldaki Queries bölmesinde yardımcı sorgular grubu belirir; asıl sorgunuz birleşik tabloyu verir.
• Veri türlerini ayarlayın, gereksiz sütunları kaldırın ve Kapat ve Uygula ile yükleyin.

Power Query Online'da akış benzerdir; ancak klasör şirket içindeyse bir veri ağ geçidi adı girmeniz ve kimlik doğrulama türünü seçmeniz gerekir.

Dosyaları birleştirmeden önce süzmek

Klasörde istemediğiniz dosyalar olabilir: geçici Excel dosyaları, eski sürümler, başka formatta yedekler ya da alt klasördeki arşiv. Combine tuşuna doğrudan basarsanız hepsi aynı muameleyi görür ve bir dosya yapı olarak uymuyorsa hata verir. Bu durumda Combine yerine "Transform Data" seçin ve dosya listesini önce süzün.

Süzme için dosya tablosundaki alanlar kullanılır. Uzantı sütununa göre yalnızca .csv ya da .xlsx dosyalarını bırakmak, klasör yolu sütununa göre bir alt klasörü dahil etmek ya da dışlamak, ad sütununa göre belirli bir kalıba uyan dosyaları almak en yaygın süzmelerdir. Tarih sütunuyla yalnızca belirli dönemden sonraki dosyaları almak da mümkündür.

Süzülmüş listeyi birleştirmek için Content sütununu seçin ve Giriş sekmesindeki Birleştir grubundan "Combine files" komutunu kullanın; ya da Content sütun başlığındaki birleştir simgesine basın. Süzme adımı sorgunun bir parçası olarak kaydolur, bu yüzden her yenilemede otomatik uygulanır.

Oluşan yardımcı sorguları anlamak

Birleştirme sonrası Queries bölmesinde birkaç yeni öğe belirir. Bunları tanımak, sorun çıktığında yön bulmanızı sağlar.

Önce örnek sorgu vardır; Transform Sample file adıyla görünür ve örnek dosyanın tek başına nasıl okunduğunu gösterir. Sonra bir fonksiyon sorgusu bulunur; örnek sorgunun mantığını dosya girdisi alan bir fonksiyona dönüştürür. Bu ikisi Helper queries grubunda toplanır. Ayrıca, başlangıç sorgusunun adını taşıyan, Transform file from ön ekli bir grup oluşur ve birleştirme için kullanılan tüm bileşenleri içerir.

Bir sürpriz yapmaması gereken kural şudur: örnek sorgu, fonksiyonla bağlantılıdır. Örnek sorguya eklediğiniz her adım (bir sütunu silmek, tür değiştirmek, bir değeri temizlemek) fonksiyona da yansır ve böylece tüm dosyalara uygulanır. Yani temizliği asıl sorguda değil, örnek sorguda yapın; ancak sonra asıl sorgunun son adımına bakıp sütun adları ya da türleri değiştiyse tür değiştirme adımının hata vermediğini doğrulayın.

Yenileme ve paylaşım

Klasör sorgusunun asıl faydası, yenilemede ortaya çıkar. Klasöre yeni dosya eklediğinizde sorguyu yenilemeniz yeter; Power Query dosya listesini yeniden okur, yeni dosyaya da fonksiyonu uygular ve birleşik tabloya ekler. Dosyayı silerseniz tablodan da çıkar. Bu yüzden klasörü bir veri deposu gibi düşünün: ne koyarsanız rapora o girer.

Bulutta paylaşırken dosya yolu önemlidir. Yalnızca kendi bilgisayarınızdaki bir klasöre bağlı sorgu, Power BI Service'te kendi başına yenilenemez; yerel dosyalar için veri ağ geçidi gerekir. Dosyalar SharePoint ya da bulut depolamadaysa bu kısıt oluşmaz, ancak o durumda doğru bağlayıcıyı kullanmak gerekir. Başka bir kullanıcıya sorguyu verdiğinizde de yol aynı olmayabilir; yolu bir parametreye bağlamak taşımayı kolaylaştırır.

Dosyaların adlandırma düzeni de ileriye dönük iş yükünü belirler. Dosya adına dönem bilgisini koyarsanız, birleştirme sonrası bu bilgiyi yeni bir sütun olarak çıkarıp tarih ekseni olarak kullanabilirsiniz.

Sık yapılan hatalar

• Yapısı farklı dosyaları aynı klasöre koymak: sütun adı ya da sıra farklıysa birleşik tabloda boş sütunlar veya hatalar oluşur. Dosya şablonunu standartlaştırın.
• Geçici dosyaları fark etmemek: açık Excel dosyasının geçici kopyası klasörde durur ve birleştirmeyi bozar. Uzantı ve ad üzerinden süzün.
• Başlık satırını birçok kez tablo içine almak: her dosyadan gelen başlık satırı veri olarak kalırsa ilk satırı üst bilgiye yükseltme adımı örnek sorguda yapılmalıdır.
• Örnek dosyayı rastgele seçmek: örnek dosyadaki Excel tablo ya da sayfa adı diğer dosyalarda yoksa hata alırsınız. En temsili dosyayı seçin.
• Dönüşümü asıl sorguda yapmak: örnek sorguda yapılmayan adımlar her dosyaya uygulanmaz.
• Tür değiştirme adımının hatasını görmezden gelmek: sütun adları değiştiyse son adım adım düzeyinde hata verir.
• Alt klasörleri hesaba katmamak: arşiv klasörü ya da eski yıl dosyaları da birleşir; klasör yoluna göre dışlayın.

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

Uygulamalı Power BI ile Veri Analitiği ve Görselleştirme Sertifika Programı, veri analizi ve işleme teknikleri ile Power BI ile veri entegrasyonu başlıklarıyla farklı kaynaklardan veri alma ve modelleme becerisini kapsar. İleri Excel programındaki veri bağlantıları ve dış veri aktarma konusu ise aynı mantığı Excel tarafında kullanan raporlama ve finans ekiplerine hitap eder.