Veri kaynaklarında bulunan tabloları zaman zaman birleştirme ihtiyacı duyarız. Bazı ERP tasarımlarında da şöyle durumlar var: Misal, faturanın tutar bilgisi bir tabloda, miktar bilgileri bir başka tabloda, aradaki ilişki de Tarih + Belge Numarası + Belge Tipi gibi sütunların kombinasyonu üzerinden tutuluyor. Tabloları birleştirmek için kullanabileceğimiz merge ve bir başka yazıda anlatacağım append gibi yöntemler var Power BI ‘da.
Önce basit bir örnekle başlayayım. Farklı / aynı tarihlerde iki farklı transaction tablosu var: Basınç ve Sıcaklık gibi.
Sıcaklık bilgisi günde sadece bir kez alınmış, basınç bilgisi ise bazen günde birden fazla kez ölçülmüş (4.02.2019). Her iki tablodaki transaction’ları tek bir tabloda birleştirmek istiyoruz!
Farklı tabloları anahtar sütunlar üzerinden birleştirmenin Power BI’daki karşılığı, Power Query tarafındaki “Merge” işlemi. SQL’deki tüm join tiplerini aynen Power Query tarafında da yapabiliriz.
Tüm günleri içeren bir Tarih tablosuna, önce sıcaklık, sonra da basınç verilerini getirelim.
Power Query tarafındaki Home tabında bulunan “Merge Queries” menüsünden “Merge Queries” veya “Merge Queries as New” seçiyorum. Aradaki tek fark birinin yeni bir sorgu oluşturacak olması, diğerinin ise mevcut seçili tabloyu kullanacak olması.
Tek bir stepte aynı anda sadece 2 tabloyu merge edebiliriz. Her iki tablodan da ortak sütunu, yani Tarih sütununu seçiyorum. “Join Kind” menüsüne gelince: Burası tam da SQL’deki join tiplerinin karşılığı. Hepsini tek tek açmaya gerek yok diye düşünüyorum ama özetlemek gerekirse, soldaki tablodan (bu durumda ilk seçili olan üstteki tablo oluyor) tüm kayıtları ve sağdaki (alttaki) tablodan sadece eşleşenleri getirebiliriz. Soldaki ve sağdaki tablodan sadece eşleşenleri getirebiliriz. Soldaki tabloda olup sağdakinde olmayanları getirebiliriz (anti joins) vs.
Anahtar sütunların her iki tabloda da veri tiplerinin aynı olması gerekiyor. Seçimi yaptığımızda tablolardan karşılıklı kaç satırın eşleştiğini görmek mümkün.
Bu yazıda girmeyeceğim ama, bir kaç ay önce gelen Fuzzy Merge ile tam birebir eşleşmenin olmadığı ama birbirine benzer değerler içeren sütunlar üzerinden de “join yapmak” mümkün.
Tabloları birleştirdiğimizde karşımıza gelen ekran aşağıdaki gibi:
Soldaki tablonun (Tarih) yanına, Basınç tablosunun satırlarını içeren bir tablo geldi. Bu tabloda ne yapacağız? Burada birleştirdiğimiz tablolardaki veri yapısını bilmek önemli.
İki opsiyonumuz var, gelen basınç tablosunu ya Expand edebiliriz, ya da Aggregate edebiliriz.
Eğer sağdaki tabloda, Tarih tablosunun her bir satırı için sadece tek bir satırlık karşılık olduğunu biliyorsak Expand etmek yeterli. Expand ettiğimizde, eğer sağdaki tabloda birden fazla karşılık varsa, Tarih tablosunun satırları resimde görüldüğü gibi çoklanacaktır. 4.02.2019 tarihinde basınç tablosunda 150 ve 130 olarak iki kayıt var, Expand edince her ikisini de getirdi. (Aynen SQL’de de olacağı gibi!)
Bu durum genelde istemediğimiz bir durumdur ve genelde birden fazla karşılık varsa gelen satırları Aggregate ederiz, ortalamasını alırız, maksimumunu alırız vs.
Bu yüzden “Aggregate” opsiyonunu seçiyorum. Listede sadece “Σ Sum of Basınç” yani toplam getireceğim demesine takılmayalım, daha sonra değiştirebiliriz.
4.02.2019 tarihindeki iki değeri toplayarak getirdi.
Peki ya toplam değil de başka bir işlem yapmak istiyorsak ne yapacağız? Bir önceki stepin formülünü istediğimiz başka bir formülle değiştireceğiz!
“List.Sum” fonksiyonunun yerine örneğin List.Average, List.Max, List.Min kullanabiliriz.
Power Query’de bol miktarda liste fonksiyonu mevcut, kullanabileceğimiz başka fonksiyonlar için bu sayfaya göz atabilirsiniz.
Tek bir stepte en fazla iki tabloyu birleştirebiliriz dedim ama birden fazla step oluşturarak istediğimiz kadar tabloyu “join”, Power Query jargonuyla “merge” edebiliriz.
Aynı mantıkla mevcut tabloya “Sıcaklık” tablosunu da ekleyip, her iki transaction tablosunu da kolaylıkla birleştirebiliriz.
** Power Query’deki formül penceresi hiç gözükmüyorsa ayarlardan “Display the Formula Bar”‘ı açmanız gerekir.
Biraz daha karışık bir örnek yapalım: Eğitimlerde kullandığım bütçe örneğini paylaşayım. Tüm bütçelerin formatları aslında hep aynı! Soldaki sütunlar bütçenin üzerinde yükseldiği cümleler, OCAK-ŞUBAT…-ARALIK sütunları aynı! Ve genelde birden fazla hedefleme var, hem miktar hem ciro vs gibi.
Bu Excel formatını her bir hedef sayfası için uygun forma çevirdiğimizde elimizde aşağıdaki gibi tablolar oluyor:
Birinde Ciro hedefi var, diğerinde Miktar hedefi! Her iki tabloda da yapı aynı sadece hedef sütunları değişiyor. İki tabloyu birleştirip yeni ve tek bir bütçe tablosu oluşturmak mümkün: Fakat bunu yaparken birden fazla sütun kombinasyonu üzerinden tabloları birleştirmemiz lazım.
Her bir tablodaki Bölge+Kategori+Tarih kombinasyonunun öteki tabloda bir karşılığı var!
Bu tür birden fazla sütun kombinasyonu üzerinden merge işlemi yapacaksak her iki tabloda da ilgili sütunları aynı sırada seçmek lazım. Control tuşuna basılı tutarak yaptığımız çoklu sütun seçimlerinin sırası her iki tabloda da aynı olmalı. Seçtiğimiz sütunların sıra numarası hemen yanlarında gözükecektir.
** Yukarıdaki bütçe örneğini daha önce bir webinar’da kaydetmiştik, izlemek isterseniz buradan.
Eğer bu “merge” işlemlerini yaparken kullandığımız veri kaynağı SQL gibi nizami bir veri kaynağıysa yaptığımız tüm hareketlerin SQL kodunu Power Query bizim adımıza oluşturuyor zaten!
SQL sorgusu yazabiliyorsanız, yazabildiğinizi unutun dememin gerekçelerinden biri de bu aslında.
…
Konuyla ilgili zaman zaman gelen sorulardan biri de şu: Tam eşitlik değil de >= , <= gibi durumlarda ne yapacağız? Örneğin bir tabloda USD kurunuz ve tarihiniz var, diğer kur tablosunda ise tarih aralığı var, iki tabloyu nasıl “join” edeceğiz.
İlk ve en önemli yorumum şu olacak, böyle bir yapıyı iki tabloyu birleştirerek çözmeyi düşünüyorsanız henüz “veri modeli ne demek” tam içselleştirememişsiniz demektir. Her bir spesifik soru için uygun formatta sorgu hazırlamaktan vazgeçmeye çalışmanızı önereceğim naçizane. İlişkilerin Power BI’da SQL’e benzer şekilde ama biraz daha farklı olarak nasıl çalıştığını anlamaya çalışmak lazım. Tabii bunun için sadece ilişkiler değil fonksiyonlardan da haberdar olmak lazım. Benzer bir probleme ilişkin bir yöntemi bu yazıda bulabilirsiniz.
…
Power BI’da bir konuyu çözmenin genellikle birden fazla yolu var. Tablo birleştirmeleri de böyle. Join tipleri, merge fonksiyonları sadece Power Query’de yok, DAX tarafında da var. NATURALINNERJOIN, NATURALLEFTOUTERJOIN, CROSSJOIN gibi fonskiyonlar var. Hatta LOOKUPVALUE gibi son derece esnek olabilecek fonksiyonlar bile “join” yaratmak için kullanılabilir. Kişisel olarak DAX tarafındaki join fonksiyonlarını neredeyse hiç kullanmadım. Power Query’dekileri ise sıklıkla kullandım.
Yazıdaki modeli indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!