Bir önceki yazımda Power Query ile bir dizinde yer alan dosyalardan veri çekmeyi göstermiştim. Bu yazıda ise yine Power Query kullanarak aynı dosyadaki tüm sayfalardan veri çekme işlemine bir bakmak istiyorum. Öncelikle bahsetmek istediğim konu bu işlem için birden fazla yöntem var. Ben burada en kolay olan yöntemi göstereceğim, ancak bu yöntemin çalışabilmesi için standartlaşma gerekiyor. Zaten otomasyonun birinci adımı standartlaşmadır.
1. Hazırlık
Ben bir önceki yazımda kullandığım değerleri kullanacağım. Buradan örnek için kullandığım dosyayıları indirebilirsiniz. AnaRapor dosyası üzerinde çalışacağımız dosya ve 3 tane sayfa ile bu sayfalarda birer tablo göreceksiniz. Bu tabloların isimleri Hafta_No_Rapor şeklinde bir standarda sahip. Standart derken de bunu kastediyorum. Verilerinizin belli başlıklara ve belli isimlere sahip olaması hem programlamada hem de Power Query ile veri çekmede size büyük kolaylık sağlar.
İkinci dosyayı son aşamada kullanacağız. O yüzden şimdilik açmanıza gerek yok. Sorguyu güncellemek için ek veriler içeriyor.
Eğer siz uygulamada kendi dosyalarınızı kullanacaksanız sizin dosyalarınızda da verileriniz aynı başlıklara sahip olsun ve standart isimlerde tablolar oluşturun.
2. Uygulama
a. Veri Kaynağını Tanımlama
Dosyanızı açtıktan sonra yeni, boş bir çalışma sayfası oluşturun. Ardından Veri Al, Diğer Kaynaklardan, Boş Sorgu seçeneğini seçin. Karşınıza boş bir Power Query ekranı gelecektir. Üstteki formül çubuğuna aşağıdaki formülü yazın:
=Excel.CurrentWorkbook()
Yazım esnasında çok dikkatli olmanız gerekmektedir. Excel Power Query M dili adında bir dil kullanmaktadır ve VBA’dan farklı olarak bu dil büyük küçük harf ayrımına sahiptir. Ve ne yazık ki formüllerde oluşan yazım hataları karşısında net bir açıklama veya Excel formüllerinde olduğu gibi otomatik formül önerisi yapmaz.
Bunu yazdıktan sonra Power Query açık olan dosyadaki tüm alanları tarayarak burada gösterecektir. Bu alanlar tablolar, tanımlı isimler hatta yazdırma alanları olabilir. Çünkü siz bir yazdırma alanı belirlediğinizde Excel arka planda YazdırmaAlanı isminde bir ad tanımlamaktadır.
Örnek dosya üzerinde işlem yaptığınızda aşağıdaki gibi bir görüntü elde edeceksiniz:
Dikkat ederseniz “Content” yazan sütunun sağ tarafında iki tarafa açılan oklar var. Bu oka bastığınızda tabloların içeriği sorguya eklenir. Eğer tablo içeriğinin bir ön izlemesini isterseniz ilgili satırda ismin yanındaki boş beyaz bölgeye tıklayabilirsiniz. Alt tarafta tablonun içeriğini gösteren bir alan açılacaktır. Yalnız tablo isminin üzerine tıklamamaya dikkat edin. Yoksa bahsettiğim düşme gibi tüm içeriği alırsınız. Daha yapılacak bir kaç işlem var.
b. Ek Sütunlar Ekleme
Dikkat ederseniz burada tablo isimlerinde hafta ve yıl bilgisi var. Bu bilginin hafta başı ve hafta sonu olarak işlenmesi gerektiğini düşünelim. Bunu yapmak için bana hafta numarası ve yılın ayrı sütunlar olarak ele alınması gerek. Hemen sütuna sağ tıklayarak “Sütunu Böl”, “Sınırlayıcıya Göre” seçeneğini seçin ve “_” karakterine göre tüm yenilemelerde bölme işlemini yapın. Artık elimizde Hafta, No ve Yıl içeren 3 tane sütun var.
Hafta değerini içeren sütun işimize yaramıyor. Bu sütunun başlığına tıklayarak “Kaldır” seçeneğini seçin. Sonrasında “Name.2” sütununun ismini “Hafta”, Name.3″ sütununun ismini de “Yıl” olarak değiştirin.
Şimdi artık yeni bir sütun ekleyebiliriz. Bunun için düzenleyicinin üst kısmındaki şeritte “Sütun Ekle” sekmesine gelin ve “Özel Sütun” seçeneğine tıklayın. Gelen formül penceresine aşağıdaki formülü girin:
Date.From(Number.From(Date.AddDays(#date([Yıl],1,1),-3))-Date.DayOfWeek(#date([Yıl],1,3))-2+[Hafta]*7)
Bu hafta numarasından hafta başlangıç tarihini bulmak için kullanılan standart bir formüldür. Yeni sütun adı kutusuna da “Başlangıç Tarihi” yazın. Bu pencereyi Tamam diyerek kapattığınızda yeni sütunumuz oluşacak. Artık hafta ve yıl sütunlarına da ihtiyacımız kalmadığından onları da kaldırabiliriz. En son bir sütun daha ekleyeceğiz buna da “Bitiş Tarihi” olarak adlandıracağız. Formül çubuğuna:
Date.AddDays([Başlangıç Tarihi]),6)
formülünü yazın. Bu bir önce oluşturduğumuz tarihe 6 gün ekleyecektir. Aşağıdaki resimlerde ilk yazdığımız formülü ve sorgunun son halini görebilirsiniz.
c. Verilerin Excel'e Aktarılması
Şimdi ilk sütundaki çift taraflı oka basarak tüm tabloların içeriğini düzenleyiciye aktarın. Tabloların içindeki sütunlar ve tüm veriler bu ekrana aktarılır. Biz burada Yeni gelen sütunların adını, ilgili veriye göre değiştirelim. Ve kolon isimlerinin solundaki yazılara tıklayarak bunların biçimini, sütun içeriğine uyacak şekilde güncelleyeceğiz. Üretim, Hedef sütunları tam sayı, verim yüzde, Başlangıç ve bitiş tarihleri tarih biçiminde olacak şekilde değiştirin. Ben sorgu adını da “Aylık Rapor” olarak güncelledim. Sorgunun son halini aşağıdaki resimde gösteriyorum.
Bunda sonra “Giriş” sekmesindeki “Kapat ve Yükle” seçeneğinin yanındaki ufak oka basarak mevcut sayfada A1 hücresine yükleme yapmayı seçiyorum. Sorgu çalışacak ve verileri yeni sayfadaki A1 hücresinden itibaren getirecektir. Ancak sorgu kısmına baktığımızda burada bir hata olduğunu görüyoruz.
Bu hatanın kaynağını görmek için “Sorgular ve Bağlatılar” alanındaki sorgu ismine çift tıklayın, sorgu düzenleyicisi tekrar açılacaktır. Eğer siz bu alanı kapattı iseniz “Veri” sekmesinden tekrar açabilirsiniz.
Sorgu penceresinde, verileri aşağıya doğru kaydırdığınızda hataları göreceksiniz. Bunun sebebi sorgu sonucu oluşan tablonun da sorguya kaynak atanması aşamasında eklenmiş olması. Ve sorgumuz tablo isimlerinden hafta ve yıl adı olmak üzere iki tane sütun elde ediyor olması. Yeni eklediğimiz tablonun ismi “Aylık Rapor” olduğundan hafta ve yıl değerlerini barındırmıyor ve bu değerlere dayanarak hesaplanan başlangıç ve bitiş tarihlerinde hata oluşuyor.
Bunu engellemek için sorgu düzenleyicinin sağ tarafında bulunan “Uygulanan Adımlar” kısmında “Kaynak” adımına tıklayın ve “Name” sütununun yanındaki aşağı ok düğmesine basarak Metin Filtreleri, İle Başlar seçeneğini seçin. Gelen pencereden “Hafta” seçeneğini seçin.
Veya en son adımda iken başlangıç veya bitiş tarihi sütunlarından birine sağ tıklayın ve hataları kaldır seçeneğini seçin ancak bu seçeneği seçerseniz bundan sonraki güncellemelerde herhangi bir sorun oluşursa sorguyu tekrar kontrol etmeniz gerekebilir..
Bu değişikliği yaptıktan sonra artık sorgumuzu tamamlamış olduk. Kaydet ve yükle diyerek verileri tablomuza aktaralım.
Dikkat ederseniz uygulama esnasında bu hatanın oluşmasını istedim çünkü veri çekerken benzer şartlar sürekli oluşabilir, ve siz hataya sebep olabilecek her türlü ihtimali ön görerek tedbirlerinizi almalı ve nihai sorguda hata oluşmasını engellemeniz gereklidir.
d. Yeni Verilerin Eklenmesi ve Sorgunun Güncellenmesi
Bir önceki örnekte olduğu gibi veri eklemek için bu sefer örnek dosyadaki Hafta_04 dosyasını açıyorum ve mevcut sayfayı kopyalayarak AnaRapor dosyasına ekliyorum. Sonrasında uygun tablo ve isim tanımlamalarını yapıyorum ve “Veri” sayfasından “Tümünü Yenile” komutunu seçiyorum ve yeni eklediğim veriler tabloma aktarılıyor. Aşağıda verit tablosunun son halini görebilirsiniz. En son satırlara dikkat ettiğinizde ek verilerin eklenmiş olduğunu göreceksiniz.
3. Faydaları, Eksiklikleri
Buradaki örnek oldukça basit veriler üzerinde olduğu için sizi yanıltmasın. Çok daha fazla verinin ve sütunun olduğu raporlarda alt yapıyı bir kere kurmak zahmetli olabilir. Ancak alt yapıyı kurduğunuzda bundan sonraki aşamalarda çok hızlı bir şekilde verilerinizi birleştirebilirsiniz.
Excel formülleri ile kıyaslandığında işlem ilk başta karmaşık gelebilir ancak alıştığınızda oldukça hızlı olduğunu göreceksiniz. Ek olarak her veri eklendiğinde formülleri yeni sayfadaki verilere göre değiştirmeniz gerekmeyecek.
Power Query için M dili adında bir sorgulama dili kullanılıyor. Bu dil sadece Power Query’de değil Microsoft’un geliştirdiği bir başka program olan ve çok güzel grafikler ve gösterge raporları hazırlamanızı sağlayan Power BI programında da kullanılabiliyor olması. Power BI ismini ilk kez duyuyorsanız internetten araştırmanızı şiddetle tavsiye ederim. Ücretsiz olarak kurabileceğiniz bir sürümü de mevcut.
M dili çok yeni bir dil. Sürekli güncellenmekte ve sınırlı sayıda kaynak mevcut. İnternette araştırdığınızda çoğunlukla karşınıza İngilizce kaynaklar çıkacaktır.
Ve tabii en büyük sıkıntı düzenleyicinin henüz çok ham olması. VBA düzenleyicisi ya da Excel gibi yazım esnasında yardımcılar ve formül önerilerini henüz göstermiyor. Ama dediğim gibi sürekli güncellendiği için bu işlevler de zamanla eklenecektir.
Ekleme: Excel 365 sürüm 1912 sonrasında Power Query düzenleyicisine sınırlı da olsa akıllı yazım yardımcısı eklendi.