Power Query ile Unpivot: Ay Sütunlarını Satırlara Çevirmek
Unpivot, yan yana duran ay ya da yıl sütunlarını Attribute ve Value sütunlarına çevirerek veriyi analize uygun uzun biçime getirir. Üç komut arasındaki fark, kaynağa yeni sütun geldiğinde ortaya çıkar.
Bir bakışta
- Unpivot, sütunları öznitelik-değer çiftlerine çevirir. Başlıklar Attribute, onların altındaki değerler Value sütununa iner.
- Uzun biçim filtre, grup ve grafik için doğal biçimdir. Ay tek sütunda durunca aya göre dilimlemek ve zaman eksenli görsel kurmak kolaylaşır.
- Yeni sütun gelebilecek kaynaklarda Unpivot other columns kullanın. Seçmediğiniz her sütun dönüşür; yeni ay otomatik satıra iner.
- Only selected komutu yeni sütunu dönüştürmez. Yeni ay raporda görünmeyebilir; bilerek seçmediyseniz tehlikelidir.
- Dönüşümden sonra adlandırma ve veri tipi şarttır. Attribute tarihe, Value sayıya çevrilmezse sıralama ve özetleme bozulur.
Neden yan yana duran aylar analizi zorlaştırır
Birçok kaynak dosya, ülke ya da ürün satırlarını ve her ayı ayrı bir sütunu olacak şekilde sunar: Ocak, Şubat, Mart sütunları yan yana durur. Okumak kolaydır ama analiz etmek zordur. Aya göre filtreleyemez, tek bir eksende grafik çizemez ve yeni bir ay eklendiğinde formülleri ve görselleri güncellemeniz gerekir.
Unpivot (özet kaldırma) bu yapıyı tersine çevirir. Microsoft belgelerinin tanımıyla sütunları öznitelik-değer çiftlerine dönüştürür; sütunlar satır olur. Power Query her zaman iki yeni sütun üretir: Attribute, unpivot edilen sütunların başlıklarını; Value ise bu başlıkların altındaki değerleri taşır.
Sonuçta ülke sütunu aynı kalır, aylar tek bir sütuna iner, değerler de başka bir sütunda toplanır. Bu "uzun" biçim, filtre, grup ve grafik için doğal veri şeklidir.
Aylar sütundan satıra inince veri analize hazırlanır
Geniş tablodan uzun tabloya
Adım adım unpivot
Power Query Editor'da kaynak tabloyu açın. Dönüştürülmeyecek kimlik sütunlarını (ülke, ürün gibi) belirleyin; aylar ve yıllar gibi yan yana duran değer sütunları dönüştürülecek olanlardır.
Dönüştüreceğiniz sütunları Ctrl ile seçin. Sonra sütun başlığına sağ tıklayıp Unpivot columns komutunu seçin ya da Transform sekmesindeki aynı komutu kullanın. Tablo hemen öznitelik ve değer sütunlarına dönüşür; sorgu adımlarında yeni bir adım belirir.
Son olarak sütunları anlamlı adlandırın: Attribute sütunu için "Ay", Value için "Satis" gibi. Sonra veri tiplerini ayarlayın. Ay başlıkları tarihse tarih tipine, değerler sayıysa sayıya çevirin; yoksa Attribute metin kalır ve sıralama alfabetik olur.
Ay adları Ocak, Şubat gibi metinse bir ay sıra numarası sütunu eklemek, görselde takvim sırasında göstermenizi sağlar. Bu bir Power Query kuralı değil, raporlama alışkanlığıdır.
Önce sabit sütunları ayırın, sonra dönüştürün
İşlem sırası
- Kimlik sütunlarıDönüşmeyecek sütunları belirleyin.
- Unpivot komutuSütunları seçip komutu çalıştırın.
- AdlandırmaAttribute ve Value sütunlarına anlamlı ad verin.
- Veri tipiAy sütununu tarihe, değeri sayıya çevirin.
Üç komut, farklı yenileme davranışı
Power Query'de unpivot için üç komut vardır ve fark, sorgu yenilendiğinde ortaya çıkar.
- Unpivot columns: seçtiğiniz sütunları dönüştürür
- Unpivot other columns: seçmediğiniz tüm sütunları dönüştürür, seçtiklerinizi sabit bırakır
- Unpivot only selected columns: yalnızca seçtiğiniz sütunları dönüştürür
Microsoft belgesi şunu vurgular: Unpivot other columns, sütun sayısı bilinmeyen kaynaklarda çok önemlidir. Kaynak tabloya yeni bir ay sütunu eklendiğinde, kimlik sütunu dışındaki her şey zaten dönüştürüleceği için yeni sütun otomatik olarak satıra iner. Belgeye göre Unpivot columns komutu da bu örnekte yenileme sonrasında yeni sütunu dönüştürür.
Unpivot only selected columns ise tam tersini yapar: yeni gelen sütun, siz seçmediğiniz için olduğu gibi kalır. Bu davranış istenen bir şey olabilir; örneğin tablonun bazı sütunlarına dokunulmaması gerekiyorsa. Ama ay sütunları her ay eklenen bir rapor için tehlikelidir: yeni ay raporda görünmez.
Kural olarak, kaynak yapısı değişebiliyorsa niyeti en açık olan Unpivot other columns'ı tercih edin ve yenilemeden sonra denemeyi unutmayın.
Örnek: bölge bazlı aylık satış tablosu
Satış ekibi size her ay güncellenen bir dosya gönderiyor: satırlarda bölgeler, sütunlarda Ocak, Şubat, Mart gibi aylar. Bu dosyayı Power BI'a alıp bölge ve aya göre dilimlemek istiyorsunuz.
Bölge sütununu seçin, sağ tıklayıp Unpivot other columns'ı çalıştırın. Attribute sütununu "Ay", Value sütununu "Satis" olarak adlandırın. Sonuçta her satır bir bölge-ay çifti ve ona karşılık gelen satış tutarını taşır. Bu tablo, ay dilimleyicisi, bölge karşılaştırması ve zaman içindeki eğilim görselleri için doğrudan kullanılabilir.
Dosyaya yeni bir ay sütunu eklendiğinde ve sorgu yenilendiğinde, o ay otomatik olarak yeni satırlar olarak gelir. Sorguyu değiştirmeniz gerekmez. Boş hücreler null olarak taşınır; gerekiyorsa boş hücreleri doldurma ya da boş değerleri atma adımını sonraya ekleyebilirsiniz.
Bu düzen, sonraki adımları da sadeleştirir. Aylık toplamı almak, bölgeye göre ortalama hesaplamak ya da son iki dönemi karşılaştırmak için artık her ay sütunu için ayrı formül yazmanız gerekmez; tek bir ölçü Value sütununu özetler ve Ay sütunu filtre görevi görür. Yeni bir ay geldiğinde raporun ölçüleri ve görselleri değişmeden çalışmaya devam eder.
Tersi de mümkündür: uzun tabloyu rapor sunumu için yeniden geniş hâle getirmek isterseniz Power Query'nin Pivot Column komutu vardır; ama raporda bunun için çoğu zaman matris görseli yeterlidir.
Sık yapılan hatalar
Birinci hata, unpivot'tan önce başlık satırlarını düzeltmemektir. İlk satır başlık olarak yükseltilmediyse Attribute sütunu anlamsız, otomatik üretilmiş başlıklar taşır. İkincisi, toplam satırlarını ya da toplam sütunlarını dönüştürmeye dahil etmektir; Genel Toplam sütunu da bir ay gibi satıra iner ve toplamlar çift sayılır. Dönüşümden önce toplam sütunlarını kaldırın.
Üçüncü hata, Value sütununun tipini ayarlamamaktır. Metin kalırsa sayısal özetleme çalışmaz. Dördüncüsü, Attribute sütunundaki tarihleri metin bırakmaktır; zaman zekası ölçüleri tarih tipi ister.
Beşincisi, yalnızca seçilen sütunları dönüştürme komutunu seçip yeni ayın gelmediğini fark etmemektir.
Bir ayrım daha: unpivot ile Excel ya da SQL'deki pivot işlemi birbirinin tersidir. Excel tarafında pivot tablo, SQL tarafında pivot sorgusu satırları sütunlara çevirir; Power Query'deki unpivot sütunları satırlara çevirir. Tersine çevirme gereken yerde hangi araçta olduğunuza bakın.
Bu beceriyi Mindset programlarında nerede çalışırsınız
Uygulamalı Power BI programında veri analizi ve işleme teknikleri başlığı, veriyi analize hazırlamanın temelini oluşturur; Power BI ile veri görselleştirme eğitiminde de veri alma ve basit dönüştürme başlığı vardır. Unpivot, farklı biçimlerde gelen Excel raporlarını düzenli bir modele çevirmek zorunda kalan analistlerin en sık kullandığı adımlardan biridir.
İlgili Mindset programı
Uygulamalı Power BI ile Veri Analitiği ve Görselleştirme Sertifika Programı
Sık sorulan sorular
Unpivot ile Transpose aynı şey mi?
Hayır. Transpose, tablonun tamamını çevirir: satırlar sütun, sütunlar satır olur ve başlıklar veri hâline gelir. Unpivot ise seçtiğiniz sütunları öznitelik-değer çiftlerine dönüştürür ve kimlik sütunlarını korur. Aylar gibi tekrar eden sütun gruplarını tek sütuna indirmek istiyorsanız aradığınız işlem unpivot'tur.
Unpivot sonrası ay sütunu neden alfabetik sıralanıyor?
Attribute sütunu metin tipinde gelir ve metin alfabetik sıralanır. Ay başlıkları tarihse sütunu tarih tipine çevirin. Ay adları metin ise bir ay sıra numarası sütunu ekleyip görselde ay adını bu sütuna göre sıralayın. Sıralama sorunu unpivot hatası değil, veri tipi tercihidir.
Unpivot işlemini geri alıp sütunları yeniden oluşturabilir miyim?
Evet. Power Query adımları ayrı ayrı silinebilir; unpivot adımını silerseniz tablo önceki hâline döner. Raporun sunum amacıyla geniş tablo gerekiyorsa Power Query'deki Pivot Column komutunu kullanabilir ya da modelleme sonrası matris görselinde aylar sütunda gösterilebilir. İkinci yol, veriyi uzun tutup görseli geniş kurmak, genellikle daha esnektir.
Çok büyük tablolarda unpivot satır sayısını nasıl etkiler?
Her unpivot edilen sütun, kimlik sütunlarının her satırı için bir yeni satır üretir; yani satır sayısı sütun sayısı kadar katlanır. Tablo büyükse gerçekten gereken sütunları unpivot edin, kullanılmayan ay sütunlarını önce atın. Sonuçta model büyürse veri azaltma tekniklerine de bakmak gerekir.