Tabloları Birleştirme, Append ve Dizin Okuma

Power Query ‘de tabloları birleştirmenin bir diğer şekli, tabloları alt alta, uçuca ekleme veya Power Query jargonuna göre “append” etme. Önceki yazıda bahsettiğim diğer tablo birleştirme şekli olan “merge” SQL’deki join’lere karşılık geliyor. Bu ise UNION karşılığı.

Tabloları alt alta birleştirme ihtiyacı duyduğumuz bir kaç net durum var: Örneğin farklı şubelerin/birimlerin doldurduğu formatı ve yapısı aynı Excel dosyaları var, bütçe verisi olabilir, herhangi bir transaction verisi olabilir vs. Hepsi bir yerde toplanıyor ve modelleme öncesi birleştiriliyor.

Bazı ERP’lerin tasarımında da şöyle bir durum var: Birden fazla şirket açmak istediğinizde aynı tablo yapısı şirket sayısı kadar kopyalanıyor, dolayısıyla tüm şirketleri içeren bir model kurmak istediğinizde tabloları birleştirmek gerekiyor.

Bazen de bir modelleme tekniği olarak kullanılabiliyor. Örneğin sipariş ve fatura tablolarını düşünün, her ikisinde de muhtelen “Ürün ID”, “Müşteri ID”, miktar, tutar vs gibi ortak sütunlar vardır. İki farklı transaction tablosunu, tabloları birleştirmeden önce bir “Belge Tipi” gibi sütun ekleyip “append” edebiliriz. Böylelikle tek tabloda hem siparişleri hem de faturaları modelleyebiliriz. Bu teknik, tabloların ve ihtiyacın ne olduğuna göre işleri fazlasıyla kolaylaştırabiliyor.

Peki tabloları birleştirme işlemini nasıl yapacağız?

Aşağıdaki gibi iki tablomuz var: Satışları gösteriyor, biri Şirket-1’e ait diğeri Şirket-2’ye ait. Aralarında ufak bir nüans var, birinde “Fiyat” için de bir sütun var.

Her iki tabloya da Power Query tarafında bağlandıktan sonra, Home menüsündeki Append’i kullanarak birleştirelim.

Aynı anda ikiden fazla tabloyu birleştiriyorsak “Three or more tables” opsiyonunu kullanabiliriz.

Tablolar birleştiğinde aşağıdaki daha kalabalık tabloyu oluşturuyor!

Append’in varsayılan davranışı şu: Adı ve veri tipi aynı olan sütun değerlerini alt alta ekle, herhangi bir tabloda farklı bir sütun varsa onu da getir, sütunun olmadığ tablodaki değerleri “null” yap ve tabloları birleştir. Birleştirdiğimiz tablolarda birebir aynı duplike satırlar olabilir, bunlar da gelecektir.

Yani “append” edilmiş tablonun satır sayısı her zaman için birleştirilen tabloların satır sayılarının toplamıdır. Sütun sayısı ise, tüm sütunlar ortaksa aynı olacaktır, değilse -örnekte olduğu gibi- yeni tablo bu farklı sütunları da içerir.

Sadece ortak sütunları almak istiyorsak birleştirme öncesinde fazlalık sütunları ilgili tablolardan uçurabiliriz.

Benim gördüğüm çoğu “append” ihtiyacında tabloları birleştirmeden önce bir ek sütun eklemek gerekiyordu. Satış verilerinin şirketleri farklı. Birleştirmeden önce şirket kodunu tablolara aynı sütun tipi ve ismiyle eklemek gerekiyor bu durumda.

Böylelikle raporları şirket/bölüm/şube bazında ayrıştırmak mümkün olacaktır.

Tablo birleştirme işini Power Query tarafında “Append” ile yapmak daha iyi bir çözüm ama bazen DAX tarafındaki UNION fonksiyonunu da kullanmak gerekebilir. İlgili yazı için buradan.

Bu arada verilerini birleştirdiğimiz alt tabloları Power BI tarafına yüklememeyi unutmayın lütfen. Aksi taktirde gereksiz bir kalabalık yaratmış oluruz. İlgili yazı için buradan.

…

Eğer veri kaynağı olarak Excel, txt veya csv gibi dosyalar kullanıyorsanız ve bu dosyaların formatları yapıları aynıysa dosyaların tamamını tek seferde birleştirmek mümkün!

Get Data ‘dan dosyaları tek tek göstermek yerine hepsini bir dizine (Folder) kopyalamak gerekiyor öncelikle, devamında Get Data ‘dan bu dizini veri kaynağı olarak kullanacağımızı belirtiyoruz.

Dizinin adresini giriyoruz.

Gelen menüden “Combine & Edit” opsiyonunu seçiyoruz.

Örnek dosya olarak dosyalardan herhangi birini gösterebiliriz.

OK dediğimizde tablolar gayet güzel bir şekilde birleşiyor.

Bu çok pratik bir özellik, fakat çok sevmediğim bir durum da yaratıyor! Sol tarafa bir sürü sorgu/fonksiyon ekledi!

Bu tür durumda benim kullandığım yöntem şöyle: Dizini gösterdikten sonra “Combine & Edit” opsiyonu yerine “Edit” seçiyorum.

Gelen tablo dizindeki tüm dosyaları ve dosyaların özelliklerini gösteriyor. Bize lazım olan “Content” ve “Name” sütunları. “Name” sütununu sadece verinin hangi dosyadan geldiğini anlamak için tutuyorum, yoksa buna da ihtiyacımız yok!

Diğer sütunları uçurup “Add Column” menüsündeki “Custom Column” ile yeni bir sütun  ekliyorum.

Kullanacağım fonksiyon Excel.Workbook fonksiyonu. Adı üstünde bir Excel dosyasına bağlanmamız durumunda işleri kolaylaştırıyor. Sondaki “true” opsiyonu sayfalardaki satır başlıklarını doğru okuyabilmesi için.

Excel.Workbook ([Content], true )

Eklediğimiz sütunu “Expand” ettiğimizde çıkan listeden bize “Data” ve “Kind” lazım!

** Burası önemli olabilir: Eğer bağlandığımız Excel ‘de hem sayfalar, sayfalarda da tablolar varsa Power Query her ikisini de okuyacaktır: Hem sayfayı, hem de sayfadaki tabloyu. Bu da aynı veriyi iki kez okumamıza yol açacaktır. Eğer okuduğunuz Excel’de sadece sayfalar varsa “Kind” ‘ sütununa da gerek yok. Örnek dosyada sayfalarda tablolar da olduğu için sadece bir tipi seçiyorum.

Bu aşamadan sonra bize artık sadece “Data” sütunu lazım. Diğer sütunları uçurup “Data” sütununu tamamen “expand” ediyorum.

Sütun isimlerini ve tiplerini düzelttikten sonra her şey hazır! Biraz daha fazla işlem yaptık ama en azından fazladan sorgular oluşmadı!

Son bir öneride daha bulunayım. Eğer Excel dosyası okumak sizin için zorunlu bir durumda ve çok sayıda Excel/veri okuyorsanız .xlsx, .xls formatları yerine .csv ‘yi tercih edin. Csv formatı xlsx formatına göre çok daha hızlı çalışıyor.

Yazıdaki modeli indirebilirsiniz.

Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!