List ve Merge, Kur Tablosu

Power BI 'da sık karşılaşılan durumlardan biri de yabancı para birimi cinsinden yapılan işlemlerin bir kur tablosu üzerinden TL'ye çevrilmesi. DAX ile çözmek de pekala mümkün, fakat bu tür durumlarda Power Query'nin gücünden faydalanmak daha hızlı ve kolay.

Aşağıdaki gibi bir veri setimiz var:

kur tablosu

Farklı tarihlerde farklı para birimleri üzerinden yapılmış satışlarımız var, kur tablosunda her bir gün için bir kur yok, fakat kurun geçerli olduğu bir tarih aralığı var. 1-5 Ocak tarihleri arasında USD=5,2 gibi. Tüm satışların TL cinsinden karşılığını nasıl bulacağız?

Satış satırlarına ilgili tarihteki para biriminin kurunu bulup getirmek en kolay yöntem!

Kur tablosundaki geçerli tarih aralığını ardışık günlere çevirmek gerekiyor öncelikle: Yani orijinal tabloyu aşağıdaki şekle çevirmeliyiz.

listmerge 2

Birçok uygulamada olduğu gibi -örneğin Excel- Power BI 'da da tarih sütunlarının nümerik bir karşılığı vardır. Power Query tarafında Başlangıç Tarihi ve Bitiş Tarihi sütunlarının veri tipini önce tam sayıya çeviriyorum.

listmerge 3

Başlangıç ve bitiş arasındaki rakamları içeren yeni bir liste şeklinde sütun oluşturuyorum.

= { [Başlangıç Tarihi]..[Bitiş Tarihi]}
listmerge 4

Oluşan liste sütununu, sütunun hemen yanındaki "expand" işaretini tıklayıp "Expand to New Rows" opsiyonunu seçerek açıyorum.

listmerge 5

Bu işlem, başlangıç ve bitiş tarihleri arasındaki tüm rakamları içeren yeni satırları ekledi tabloya.

listmerge 6

Oluşturduğumuz listenin veri tipini tekrar tarihe (Date) çeviriyorum. Başlangıç ve bitiş sütunlarına da artık ihtiyacımız kalmadığı için siliyorum.

listmerge 7

Tablo istediğimiz hale geldi: her bir para birimi için ardışık günleri içeren kur değerlerimiz var.

Bu kur değerlerini, satışlar tablosuna getirelim: Satışlar tablosundaki her bir satırdaki Tarih+Kur kombinasyonunun karşılığını Kurlar tablosunda bulmamızı sağlayacak işlemin adı "Merge".

Home tabındayken ve Satışlar tablosu seçiliyken menüden "Merge Queries" 'i seçiyorum.

listmerge 8

Merge işlemi, SQL'deki join yapmanın karşılığı! Sorgu yazarak yapabileceğiniz tüm joinleri, Power Query deki Merge özelliğini kullanarak çok daha hızlı ve kolay yapabilirsiniz.

listmerge 9

Satışlar tablosundaki Tarih-Para Birimi kombinasyonunu kur tablosunda bulmak için, Satışlar tablosunda önce Tarih sonra da Para Birimi sütunlarını kontrol tuşunu basılı tutarak seçiyorum. Aynı işlemi aynı sırada bu sefer Liste ve Para Birimi sütunlarını seçerek Kurlar tablosunda yapıyorum. Her iki tabloda da yaptığımız seçimin sırasını sütunların sağ üst köşesinde görüyoruz.

listmerge 10

Bu işlemi yaptığımızda Satışlar tablosundaki kaç satır için Kurlar tablosunda karşılık bulduğunu ekranda gösteriyor. 4 satırdan 3'ünü buluyor, bulamadığı satır TL olan satır, Kurlar tablosunda da TL yok zaten.

listmerge 11

Oluşan yeni sütündaki tablodan bize sadece Kur değeri lazım.

listmerge 12

Bu şekilde tabloyu açıyorum (expand).

listmerge 13

TL satırı hariç her bir satırın kuru geldi. Burada bir tasarım kararı vermek lazım, tabloyu bu haliyle Power BI tarafına aktarıp, IF ya da SWITCH ile eğer satırdaki para birimi TL değilse şunu yap, TL ise bunu  yap diyebiliriz. Bu tür durumlarda ben satır TL ise kuru 1 yapmayı tercih ediyorum, TL de bir para birimi ve kur karşılığı var!

Kurunu bulamadığımız tüm satırların TL satırı olduğundan emin olduğumuz için "null" değerleri "1" ile değiştireceğim.

"Null" olan hücreyi sağ tıklayıp açılan menüden önce "Replace Values" opsiyonunu seçiyorum ve değeri "1" yapıyorum.

listmerge 14
listmerge 15

Tablo artık tam istediğimiz formatta, Kurlar.Kur sütununun da ismini Kur olarak değiştiriyorum.

listmerge 16

Artık tabloyu Power BI tarafına aktarıp metriğimizi yazabiliriz.

Satışlar TL := SUMX ('Satışlar' ; 'Satışlar'[Satış Tutarı] * 'Satışlar'[Kur] )

Power BI 'da ve DAX 'ta bir işlemi farklı yöntemlerle yapmak mümkün, Power Query için de geçerli bu durum: tarihleri önce rakama çevirip liste oluşturmak yerine doğrudan tarihin rakam karşılığını bulacak bir fonksiyonla da yapabilirdik.

= { Number.From ([Başlangıç Tarihi])..Number.From ([Bitiş Tarihi]) }

Mantık aynı aslında, sadece kullanılan fonksiyon farklı.

Bir yöntem de, iki tarih arasındaki gün farkını alıp, başlangıç tarihinden itibaren bu gün farkı kadar tarih eklemek olabilir. Bunu yapabilmemizi sağlayacak bir fonksiyon var:

= List.Dates (<başlangıç tarihi>,<tekrar sayısı>,<süre>)

İki tarih sütunu arasındaki gün farkını, Başlangıç ve Bitiş sütunlarını seçip Add Column menüsündeki Date altından "Substract Days" opsiyonunu seçerek bulabiliriz.

listmerge 17

Sonra da fonksiyonu kullanarak aradaki günleri ekleyebiliriz.

= List.Dates ( [Başlangıç Tarihi] , [GünFarkı]+1, #duration(1,0,0,0) )

Duration kısmındaki (1,0,0,0) her seferinde tarihe 1 gün eklemek istediğimizi gösteriyor

listmerge 18

Geri kalan işlemler aynı, listeyi yeni satırlar ekleyecek şekilde açmaktan ibaret.

Aynı işlem için birden fazla örnek yapmamın gerekçelerinden biri şu, Power Query de hakikaten çok sayıda çok işe yarayabilecek fonksiyonlar var.  Merge ve Append gibi SQL tarafında yapılan işlemlerin birebir hatta fazlasıyla karşılıkları var.

Eğitimlerde sıklıkla söylediğim bir cümleyi burada da tekrarlamama müsade edin: SQL bilmek kıymetli bir artı, fakat Power BI ile çalışırken SQL sorgusu yazabildiğinizi unutun, yapabileceklerinizin tamamını Power Query 'de daha hızlı ve kolay yaparsınız.

Yazıdaki modeli indirebilirsiniz.

Sadece kayıtlı üyeler görebilir. Giriş veya Üyelik için login.

Bloga sosyal medya hesabınızla hızlı üyelik-giriş için ilgili ikonu tıklayabilirsiniz.