Daha önce farklı kitaplardan veya sayfalardan formül ile veri alma konusunda bir yazı yazmıştım. Bu yöntem, kitap içinde veri almak için güzel bir yöntem olsa da eksiklikleri var. Özellikle farklı kitaplardan veri çekerken Dolaylı formülü ile yaşanan sıkıntı buna bir örnek. Bu yazımda veri çekmek için kullanabileceğiniz daha hızlı ve daha etkili bir yöntem olan Power Query ile veri çekme işlemini anlatmak istiyorum.
Power Query 2013 yılında gelen bir özellik. Bu nedenle Excel 2010 ve 2013 için ayrı bir indirme ile eklenebiliyor. 2016’dan itibaren ise tüm Excel sürümlerinde mevcut. Eğer sizde Excel 2010 ve 2013 kullanıyor vebu eklentiye sahip değilseniz önce sahip olduğunuz sürüme göre kurulum dosyasını indirmeniz ve kurmanız gerek. Excel 2010 için power query sadece profesyonel sürüme kurulabildiğini de ekleyeyim.
Power Query ile dolaylı formülünün zaafları olmadan istediğiniz konumdan benzer alt yapıya sahip tablolardan veri çekebilirsiniz. Ve en güzeli yeni sayfalar veya kitaplar eklediğinizde tek yapmanız gereken sorguyu yenilemektir. Power Query sizin adınıza tüm değişiklikleri tarar ve ilgili verileri alarak güncel verileri çekerek gösterir. İlk aşama formül yazmaktan biraz daha zordur ancak bir kere tamamladıktan sonra nerede ise anında güncelleme yapabilmeniz çok büyük bir avantaj sağlamaktadır.
1. Hazırlık
Eğer aynı alt yapıya sahip dosyalarınız var ise onları kullanabilirsiniz. Eğer hali hazırda elinizde dosya yok ise buradan uygulama dosyalarını indirin.
İkinci olarak Excel’in verileri düzgün okuyarak birbiri ile ilişkilendirebilmesi için ortak bir belirtece ihtiyacı vardır. Bu belirteç aynı sayfa adı, aynı aralık ismi veya tablo olabilir. Ben örnekte ortak aralık ismi kullandım. Haftalık rapor dosyalarını açtığınızda her birinde Rapor adında bir isim tanımlandığını görebilirsiniz. Siz eğer kendi dosyalarınızı kullanacak iseniz farklı dosyalardaki ilgili aralıklara aynı ismi verin.
Eğer isim kullanmak istemiyorsanız, en azından ilgili sayfaların isimleri tüm dosyalarda aynı olmalıdır. Aksi takdirde Excel ilgili verileri birleştirmek için ortak bir nokta bulamaz ve indis hatası verir.
Son olarak ana dosyaya aktaracağınız verilerin olduğu dosyaların hepsini başlangıçta aynı dizine koymayın, daha sonra güncelleme yaptığımızda verilerin nasıl değiştiğini görmek için ek dosyalara ihtiyacımız var.
2. Uygulama
a. Birleştirme Seçeneklerinin Belirlenmesi
Eğer uygulama dosyalarını indirdi iseniz HafatalıkRaporlar adında bir dizin göreceksiniz. Bu dizinin altında benim haftalık tezgah verimlilik raporlarım var. Her dosyada bir de rapor adlı bir isim var. Aşağıda bir tane dosyanın içeriğini görebilirsiniz.
Uygulamayı yapmaya en baştan başlayacağım o yüzden yeni bir dosya oluşturuyorum. Veri alma seçeneği Excel’in farklı sürümlerinde farklı yerlerde, Excel 2016’da Veri sekmesinde yer alıyor. Excel 2013’te kurulum yaptıktan sonra şeritte ayrı bir sekmesi oluyor. Excel 365’te ise yine veri sekmesinde ancak ayrı bir simge olarak bulunuyor.
Bu seçeneği seçtikten sonra karşınıza dosyaların bulunduğu dizini seçmeniz için bir pencere gösterilecektir. Buradan ilgili dosyaların yer aldığı dizini seçin. Uygulama dosyalarını indirdi iseniz Veri Alma dizini altındaki HaftalıkRapor dizini. Bu seçimi yaptıktan sonra Tamam düğmesine basın. Karşınıza dizinde bulunan dosyaların listesini içeren bir bilgi ekranı gelecektir. Burada alt tarafta yer alan “Birleştir ve Düzenle” düğmesine tıklayın.
Bundan sonra gelen ekran oldukça önemli bir ekran. Burada dosyaların hangi göstergeye göre birleştirileceği sorulur. Gördüğünüz gibi burada iki tane seçenek var. Biri benim aralığa verdiğim isim, diğeri bilgilerin yer aldığı çalışma sayfasının adı. Örnek dosyada tek bir sayfa ve tek bir isim olduğundan burada sadece birer tane gözüküyor. Sizin dosyanızda daha fazla sayfa ve isim var ise burada hepsi gösterilir.
Burada dikkat etmeniz gereken husus, seçtiğiniz seçeneğin tüm çalışma kitaplarına aynı olması gerekliliğidir. Eğer sayfa adını kullanırsanız ve farklı dosyalarda veriler farklı isimdeki sayfalarda ise birleştirme işlemi gerçekleşmez.
Burada ben Rapor ismini seçeceğim. Zaten bu ismi seçtiğimde sağ tarafta ilgili ismin başvurduğu aralık ve veriler gösterilecektir. Tamam dediğimizde artık Power Query Ekranına geçiş yapacaksınız.
b. Power Query Düzenleme
Alttaki resimde size Power Query Düzenleyicisinin görünümünü veriyorum. Sizde eğer örnek uygulama dosyaları üzerinden çalıştı iseniz bu ekranlar karşılaşacaksınız. Eğer kendi dosyalarınızı kullandı iseniz verilerin yer aldığı alanda kendi sütunlarınızı ve verilerinizi göreceksiniz. Gördüğünüz gibi Excel aslında sizin yerinize çoğu işlemi yaptı. Ancak ben bu örnekte bir kaç şeyi düzelteceğim.
İlk olarak sizde Formül Çubuğu gözükmeyebilir, öncelikle bunu açmanızı tavsiye ediyorum. Bunun için Görünüm sekmesine girip en sol kısımda yer alan “Formül Çubuğu” kutusunu işaretleyin.
Bu pencerede en sağ tarafta sorgunun işlem adımlarını göreceksiniz. Bunları silebilir ve istediğiniz gibi eklemeler yapabilirsiniz. Her yaptığınız işlem buraya sırası ile eklenir. Her işlemin üzerine geldiğinizde formül çubuğunda yapılan işlemin formülünü görebilirsiniz. Ve isterseniz bu işlem adımını silebilirsiniz.
Dediğim gibi Excel sizin adınıza çoğu işlemi yapıyor ancak ben garanti olması açısından bir kaç işlem yapacağım. Önce dizine farklı bir dosyanın konulması durumunda, bu dosyanın işlenmesini engelleyecek bir filtre koyacağım. Bunun için “filtrelenmiş gizli dosyalar” adımına tıklıyorum. Düzenleyici ekranı bu adımdaki durumu gösterecektir. Ardından “Name” sütununun yanındaki ufak kutucuğa tıklıyorum ve “Metin Filtreleri” seçeneğine gelip “İle başlar” komutunu tıklıyorum. İşlem adımlarını aşağıdaki resimde görebilirsiniz.
Ardından Excel filtrelerine benzer bir pencere gelecektir. Burada ile başlar kutusunun yanına Hafta yazıyorum. Böylece bu dizine Hafta ile başlamayan bir dosya koyarsam bu dosya işleme alınmayacaktır. Siz burada birden fazla şart ekleyebilirsiniz.
Bu aşamada artık verilerimiz Excel’e aktarılmaya hazır. Ancak ben basit bir adım daha uygulamak istiyorum. Bu veriler içinde gördüğünüz gibi hafta bilgisi yer almıyor. Bu bilgi dosya adının sonuna eklenmiş. Ben burada bu numarayı okusam, sonradan Excel içinde metin formülleri ile uğraşmama gerek kalmaz.
c. Power Query İle Ek İşlemler
Bunun için dosya adının yer aldığı sütunu bölmem gerekiyor. Eğer siz nihai tablo içinde dosya ismini kullanmayacaksanız bu sütunda, eğer kullanacaksanız bu sütunu çoğaltarak çoğalttığınız sütun üzerinde bu işlemleri yapabilirsiniz.
İlgili sütuna sağ tıklayınca gelen menüden “Sütunu böl” veya sütunu seçtikten sonra araç çubuğunda yer alan “Sütunu böl” simgesine tıklayarak gelen seçeneklerde “Sınırlayıcıya göre” seçeneğini seçerek bölme işlemini yapabilirsiniz. Bölme işleminin neye göre yapılacağını soran bir ekran gelecektir. Burada metnin nereden bölüneceğini gösteren karakter otomatik olarak gelir, siz isterseniz müdahale edebilirsiniz.
Not: Power Query’nin farklı versiyonlarında bu anlattıklarım benzer adımlarla ancak farklı pencerelerle yapılabilir.
Bu işlemi yaptığınızda Source.Name.1 ve Source.Name.2 isimli iki sütun olacak. Birinci sütunda “Hafta” ifadesi ikinci sütunda geri kalan ifadeler ve .xlsx metni olacak. Şimdi bir kez daha bu işlemleri Source.Name.2 sütununda uygulayacağız. Bu sefer sınırlayıcı olarak . karakterini kullanacağız. Bu işlemleri tamamladığımızda Power Query düzenleyici ekranı aşağıdaki gibi oluşacaktır.
Burada işime yaramayan sütunların üzerine sağ tıklayıp “Kaldır” seçeneğini seçerek bu sütunları kaldıracağım. En sonunda Source.Name.2.1 sütununa sağ tıklayıp “Yeniden adlandır” diyerek “Hafta No” adını veriyorum. Artık verilerim Excel’e aktarılmaya hazır. Bunun için şerit üzerindeki “Giriş” sekmesindeki Kaydet ve Yükle simgesine tıklıyorum. Burada iki tane seçenek var. İlki açık Excel çalışma kitabında yeni bir sayfa oluşturur ve verileri oraya aktarır, diğeri ise nereye ve nasıl (Tablo, Özet Tablo, Özet Grafik vs.) ekleyeceğinizi soran bir iletişim kutusu gösterir. Ben bu seçeneği seçerek mevcut sayfada A1 hücresine tablo olarak eklemeyi seçtim. Ve dizinde yer alan dosyaların içeriği bir tablo halinde bu sayfaya geldi.
d. Tablo Düzenleme, Diğer Ayarlar ve Güncelleme
Verilerimizi aldık ancak sayılar istediğimiz biçimde gelmedi. Hemen Hedef Üretim ve Gerçekleşen Üretim sütunlarını seçerek biçimlendir diyor ve rakam biçimlendirme yapıyorum. Ardından Verim sütununu yüzde olacak şekilde biçimlendiriyorum. Eğer tablonun renklerini beğenmezseniz Tasarım sekmesinden aynı tablo biçimlendirme gibi istediğiniz biçimlendirmeyi seçebilirsiniz.
Bütün bunları yaptıktan sonra kontrol etmeniz gereken bir husus var. Eğer siz tabloyu her yenilediğinizde, yani güncellediğinzide bu biçimlendirmenin kaybolmasını istemiyorsanız yapmanız gereken son bir adım var. Tablo üzerinde herhangi bir hücre seçili iken “Veri” sekmesinden “Özellikler” seçeneğini seçin ve gelen pencerede “Sütun sıralama/filtre/yerleşim ayarlarını koru” seçeneğini seçmeniz gerekiyor.
Şimdi isterseniz örnek dosyalarının yer aldığı dizinden EkRaporlar altındaki dosyaları kopyalayarak HaftalıkRapor adlı dizine kopyalayın. Artık dizinde yeni dosyalar var ve bizim verilerimizi bu yeni dosyaları içerecek şekilde güncellememiz gerekiyor. Bunun için tablo üzerinde iken gelen yeni sekmeye (eski sürümlerde Sorgu sekmesi) gelip yenile simgesine tıklamanız gerekiyor. Bu işlemi yaptığınızda Power Query yeni eklenmiş dosyaların içindeki verileri okuyarak tüm yaptığımız işlemleri uygular ve tabloya ekler. Aşağıdaki resimde dikkat ederseniz artık 3. hafta verileri de dosyaya eklendi.
Eğer siz bu sorgu adımlarını tekrar düzenlemek üzere sorgu düzenleyiciyi tekrar açmak isterseniz veri sekmesinden “Sorgular ve Bağlantılar” seçeneğini seçin. Excel penceresinin sağ tarafında mevcut sorguların gösterildiği “Sorgular ve Bağlantılar” penceresi açılır. Burada istediğiniz sorguya çift tıklarsanız tekrar Sorgu Düzenleyicisi penceresine geçiş yaparsınız.
3. Power Query Faydaları, Zaafları
Gördüğünüz üzere Power Query ile veri çekme ilk başta biraz uğraşma istiyor. Ancak formül ile veri çekmeyle kıyaslandığında bir çok avantajı var. Öncelikle sizin dizin içine dosya attığınızda her seferinde formülleri güncellemenize gerek yok. Dolaylı formülünü kullanıyor iseniz dosyanın açık olması gerekliliği yok Burada anlattığım adımların fazla olmasına takılmayın, alıştıktan sonra tüm bu işlemler oldukça kolay ve hızlı biçimde yapılabiliyor.
Formüle göre tek bir zaafı var o da dizine yeni bir dosya attığınızda sorguyu güncellemeyi unutmamanız gerektiği. Ancak bunu bir makro ile otomatiğe bağlayabilirsiniz. yada yukarıdaki pencere üzerinde sorguya sağ tıklayarak gelen menüden “Özellikler” seçeneğine tıklayarak gelen pencerede sorguyu belli dakika aralıkları ile veya bu dosya her açıldığında yenileme seçeneklerini etkinleştirebilirsiniz. Ancak bu seçeneklerin dosyada işlem yaparken yenileme zamanı geldiğinde veya dosyayı açarken bazı gecikmelere sebep olacağını unutmayın. Özellikle çok fazla dosyanın olduğu bir dizinden güncelleme yapıyorsanız bu can sıkıcı olabilir.
Aslında bu yazıda aynı dosyadaki farklı sayfalardan veri okumayı da anlatmayı düşünüyordum ancak yazı zaten yeterince uzun oldu. Bu konu da bir sonraki yazıya kaldı. Her zaman olduğu gibi yorumlarınızı, fikirlerinizi ve isteklerinizi benimle paylaşın.
merhaba,
power query ile ilgili 1 haftadır uğraştığım bir sorun var. XML linkinden veri çekiyorum. kolonun birinde farklı değerler var. query e [Table] olarak dönüyor. genişletmeden excele verebiliyorum. fakat kolonu genişletince excel hata veriyor. bende query tablosunu kopyala yapıştır yapayım dedim. bu seferde 1000 satırdan fazla veri kopyalamıyor. yardımcı olursanız sevinirim.
sağlıklı günler.
Merhaba,
Sütun genişletme işlemi esnasında Power Query tablodaki sütun isimlerini metne çevirerek sabit bir dizi elde eder. Bu dizideki isme sahip sütunları tabloya ekler. Eğer bu isimlerde bir sorun var ise, aynı isimde halihazırda bir sütun var ise, veya o isme sahip bir sütun yok ise hata verir. Power Query hata metnini görmeden daha ayrıntılı bir şey söylemek mümkün değil.
Saygılarımla,