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:
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.
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.
Başlangıç ve bitiş arasındaki rakamları içeren yeni bir liste şeklinde sütun oluşturuyorum.
= { [Başlangıç Tarihi]..[Bitiş Tarihi]}
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.
Bu işlem, başlangıç ve bitiş tarihleri arasındaki tüm rakamları içeren yeni satırları ekledi tabloya.
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.
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.
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.
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.
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.
Oluşan yeni sütündaki tablodan bize sadece Kur değeri lazım.
Bu şekilde tabloyu açıyorum (expand).
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.
Tablo artık tam istediğimiz formatta, Kurlar.Kur sütununun da ismini Kur olarak değiştiriyorum.
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.
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
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 üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!