Power BI ile yaptığım bütçe projelerindeki ortak ihtiyaçlara devam yazısıdır! Önceki yazıya göz atmak isterseniz buradan. Bütçeler her ne kadar veri modeli anlamında birbirine çok benzese de, bütçe formatları -ki genelde hep Excel’de tutuluyor- bazen sürprizlere açık. Bazen veri kaynağında henüz olmayan, ama dosyaya gelecekte eklenecek sütunları dinamik olarak okumak gerekiyor! Dinamik sütun seçimi bütçe modellerindeki ortak gereksinimlerden biri genellikle.
Önceki yazının da konusu olan Excel’deki bütçe formatını tekrar ekleyeyim.
Ne bütçesi yaparsanız yapın, tüm bütçeler bir veya birden fazla sütunda bütçenin boyut kombinasyonlarını içerir, daha sonra Ocak-Aralık değerlerini listeler.
Bu yazının konusu için kritik cümle “Ocak-Aralık” değerlerini listeler! Peki ya bu değerler henüz veri kaynağında -yani okuduğunuz bütçe dosyasında- yoksa! Ama gelecekte eklenecekse?
Power Query ile veri kaynağından lazım olan sütunları seçerken genellikle “Select/Choose Columns” yöntemini kullanmak daha doğru. Uygulamaya “bana şu şu sütunları ver” diyorsunuz. Gerek şart, ilgili sütunların fiziksel olarak veri kaynağında olması!
Biraz daha açayım: Bütçelerin neredeyse hepsinde en az iki tipte bütçe datası giriliyor: Biri Plan, diğeri Tahmin. “Plan” tipi genelde gelecek yıl için ne yapmayı öngördüyseniz onu içeriyor, Ocak-Aralık için bir kez girildikten sonra değiştirilmiyor. Sabit. Ama “Tahmin” değişebiliyor. Nisan-Mayıs’a kadar genellikle gidişatınızı “Plan” ‘a göre karşılaştırıyorsunuz. Sonrasında duruma göre aylık/3 aylık revizeler yapmaya başlıyorsunuz. Yani “Tahmin” verisi girilmeye başlanıyor.
Örnek formatta da aynı durum var: Ocak-Aralık için boyut bazında “Plan” verisi girilmiş. 12 ay için hepsi mevcut.
Fakat aynı durum “Tahmin” verisi için böyle değil!
Haziran’dan itibaren var, daha da önemlisi yıl sonuna kadar hepsi girilmemiş! Yani tüm aylar yok, ama zamanla eklenecek!
Power Query ile Dinamik Sütun Seçimi
Her seferinde Power BI dosyasını açıp, yeni eklenen sütunları modele manuel olarak eklemek çok hantal bir yöntem olur. Bunun yerine verideki mantığı kullanarak, veri kaynağında henüz olmasa bile, belirli string değerleri içeren sütunları modele dinamik olarak eklemek lazım.
Örnek veride yüzden fazla sütun var, bize bunlardan sadece sütun isminde “(P)” ve “LTF” olanlar + boyut sütunları lazım! Ve gerekli olmayan diğer sütunları da elimine etmemiz lazım. Bunu da dinamik yapmamız lazım!
(P plan için, LTF, latest forecast, ya da tahmin anlamında)
Herhangi bir sütun isminin belirli bir string’i içerip içermediğini bulmamızı sağlayacak fonksiyonlar var PowerQuery’de. Yani M dilinde.
Bize lazım olan fonksiyonlardan biri Text.Contains() . Eğer “text içinde X değeri varsa”! Sütun isminde (P) ya da LTF geçiyorsa! kısmı için kullanacağız.
Diğer kullanabileceğimiz fonksiyonlar da liste ve tablo fonksiyonlarından Table.SelectColumns() ve List.Select().
PowerQuery’nin mevcut editörünün çok kullanışlı olduğunu iddia edemem. Geliştirilmesi gereken çok noktası var. Bir kere büyük-küçük harf duyarlı, Table.Select ‘i table.select diye yazarsanız hata verir.
Ama son derece mantıklı bir yapısı var: her step, kendinden önceki step’leri refere ederek çalışır!
Tablodan bize lazım olan sütunları bir sonraki step’te listeleyebiliriz!
Yukarıdaki kodu şöyle okuyabiliriz:
Bir önceki step’te (örneğe göre “Changed Type”) gördüğün tablonun sütunlarını listele (Table.ColumnNames), bu sütunlardan ismi sadece “(P)” , “LTF”, “Account” string’lerini içerenleri getir, isminde “Q” ve “2021” olan sütunları ise istemiyoruz! Text.Contains, en dıştaki List.Select ‘in döndüreceği sütunların condition’larını içeriyor.
Or/and/and not gibi kombinasyonları yazarken syntax’a dikkat etmekte fayda var. Text.Contains kendi içinde bir and/or yapısı sağlıyor olsa daha güzel olurdu, tek parantezde hallederdik, ama malesef sağlamıyor. Her bir condition’ı ayrı ayrı yazmak gerekiyor.
Bu listeyi oluşturduktan sonra koşula uyan sütunları da bir başka step’te Table.SelectColumns() ile çağırabiliriz.
Her step kendinden önceki step’leri refere ederek çalışır demiştim, bu son step’te refere edilen önceki step’in gene “Changed Type” olduğuna dikkatinizi çekerim. İlla ki bir önceki step’i refere etmek gerekmiyor! Daha önce oluşturulmuş bir step olması refere etmek için yeterli.
“Change Type” step’i sonucunda gördüğün tabloyu, “DinamikSutunSecimi” step’indeki kod neyse ona göre getir!
Gene ufak bir not, DinamikSutunSecimi isminde Türkçe karakter ya da boşluk yok! Eğer olursa syntax olarak hata verecektir.
Dosyaya sonradan bu koşullara uyan sütunlar eklendiğinde artık dinamik olarak modele eklenecektir.
Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!