Bu yazımda Power Query ile farklı ortamlardan alınmış olan metin veya csv dosyalarında yer alan verilerin Excel’e aktarılması işlemini göstereceğim. Bu tarz dosyaları siz Excel’e direk olarak aktarabilirsiniz tabii ancak bu dosyaların içindeki verileri bir şekilde dönüştürmeniz gerekiyor ise, özellikle metin olarak gelen verilerin başka türlere dönüştürülmes vs., Excel’in kendi formüllerini kullanarak bu işi yapmak oldukça zaman alıcı bir uğraş olabilir.
bir de bu işlemi aylık, haftalık gibi belirli aralıklarla yaptığınızı var sayalım, her seferinde bu dönüşümleri yaparak Excel raporlarında kullanılabilir hale getirilmesi hem zaman alıcıdır hem de hataya açık bir durumdur. Bu yazımda başka kaynaklardan gelen metin içerikli dosyaların Power Query yardımı ile Excel’e aktarılmasını anlatmak istiyorum.
1. Hazırlık
Bu yazıda ben bir CSV dosyasından veri çekme işlemi uygulaması yapacağım. Siz dilerseniz bir Excel dosyası veya metin dosyası kullanabilirsiniz. Excel dosyası kullanacak iseniz verilerinizi barındıran aralığı tablo olarak tanımlamanız ve belirli bir isim vermeniz çok önemli.
csv dosyasını indirmek için buraya tıklayarak 5000 sales record adlı dosyayı indirebilirsiniz. csv genel olarak bir metin dosyasıdır, içinde belli bir karakter (“,”, “sekme”,”/” gibi) ile ayrılmış farklı veriler içerir. Bu karaktere göre verileri böldüğünüzde bir tablo elde edersiniz.
Neden csv dosyası derseniz; bu dosya biçimi pek çok veri tabanı tarafından dışarıya veri aktarmak için kullanılmaktadır. Bu yüzden bu tarz kaynaklar tarafından oluşturulmuş verilerin Excel’e mantıklı değerler olarak alınması oldukça fazla karşılaşılan bir durumdur.
Ben örnekte bir kaç fazladan örnek göstereceğim için dosyanın başına bazı veriler ekledim. Eğer isterseniz siz de aşağıdaki satırları dosyaya ekleyebilirsiniz.
XYZ Company Ltd.
2010-2017 World Wide Sales Report
Generated by ENIAS System On 07/02/2018
2. Uygulama
Excel’de yeni bir dosya açalım. Ardından Veri sekmesinden Veri Al, Dosyadan, Metin veya CSV’den seçeneğini seçin. Bir dosya gezgini penceresi açılarak sizden csv dosyası seçmenizi isteyecek. Dosyayı kaydettiğiniz dizine gelerek ilgili dosyayı seçin. Karşınıza dosya içeriğini gösteren aşağıdaki gibi bir pencere gelecektir.
Aşağıdaki ekran görüntüsünün üst orta kısmında yer alan sınırlayıcı penceresinde virgül yazdığını görüyorsunuz. CSV dosyalarında bağımsız sütunlardaki verileri ayırmak için belli karakterler kullanılır. Bu sekme, virgül, bölü gibi karakterler olabilir. Power Query düzenleyicisi açtığınız CSV dosyasındaki bu ayırma karakterini otomatik olarak bulur ve burada gösterir. Eğer siz farklı bir karakter kullanıldığını düşünüyorsanız yan tarafında yer alan aşağı oka basarak bu kutuyu genişletebilir ve farklı bir karakter kullanabilir hatta özel bir karakteri (“|” gibi) kullanabilirsiniz. Ancak burada kullandığınız karakter, sorgudaki sütunların belirlenmesinde kullanılacağından dikkatli olmanızda fayda var.
Bu ekranda “Veri Dönüştürme” düğmesine bastığınızda Power Query Düzenleyicisi gelecektir. Power Query Düzenleyicisi otomatik olarak verileri sütunlara bölecek ve sütunları da verinin içeriğine göre otomatik olarak biçim atayacaktır.
Sol tarafa baktığınızda “Uygulama Adımları” adlı kısımda ilk işlem olan “Kaynak” adımını göreceksiniz. Bunun yanında yer alan ufak dişli simgesine basarak Kaynak adımının değişkenlerine erişebilirsiniz. Eğer benzer biçimlendirmeye sahip farklı bir dosya kullanmak isterseniz sadece farklı dosyayı seçmeniz yeterli olacaktır. Bu sayede uyguladığınız bütün adımları tekrar etmeden işlemlerin hepsini yeni dosyaya da uygulamış olursunuz.
Yine sol tarafta üst kısımda Ad adlı bir kutu yer alıyor. Burada Power Query otomatik olarak sorgunuza bir isim atayacaktır. Buraya hazırladığınız veri ile ilgili açıklayıcı bir isim girmek daha anlaşılır olacaktır.
Pencerenin sol tarafındaki “Sorgu Ayarları” kısmında Değiştirilen Tür adında bir işlem adımı göreceksiniz. Biz sütunlara farklı türler atayacağımız için bu işlem adımını silin.
Gördüğünüz gibi tablonun başında istenmeyen 3 tane satır var. İlk yapmamız gereken bu satırları kaldırmak ve ardından 4. satırdaki değerleri sütun ismi olarak kullanmak. Bunun için önce düzenleyici Giriş sekmesinden Satırları azalt simgesine tıklıyoruz gelen pencereye 3 değeri girip ilk 3 satırı kaldırıyoruz. Ardından yine Giriş sekmesindeki “İlk satırı üst bilgi olarak kullan düğmesine basın.
Excel tekrar tablodaki sütunlara kendine göre bir tür atayacaktır. Bu işlem adımını da silin. Şimdi isterseniz tabloyu Excel’e aktaralım ve neler olduğuna bir bakalım. Aşağıda bir ekran resmi ekledim.
Burada dikkat edeceğiniz ilk husus tarih değerlerinin metin olarak gelmesi olacaktır. Siz bu sütunun biçimini tarih olarak değiştirseniz dahi Excel değerlerin görüntülenmesini değiştirmeyecektir. Bunun nedeni biz tür değişikliklerini sildik. Şimdi isterseniz tekrar geri dönüp bu değişiklikleri yapalım. Yandaki pencereden sorgu üzerine çift tıkladığınızda tekrar düzenleyici penceresi açılır. İlk 5 sütunun biçimini metin yapın, bunun için sütun başlığının sol tarafında yer alan “ABC” simgesine tıklayın. Yeni bir menü görüntülenecek ve istediğiniz türü seçmenize olanak sağlayacak.
Şimdi “Order Date” sütununun türünü “Tarih” olarak değiştirin. Birden, bazı satırlarda “Error” (Hata) ifadesinin belirdiğini göreceksiniz. Bunun nedeni tarih değerlerinin Amerikan standardına göre Ay/Gün/Yıl biçimine göre girilmiş olmasıdır (Merak etmeyin bu da yazının bir içeriği olduğu için hata oluşmasını bilinçli olarak istedim). Ancak Excel bunu Gün/Ay/Yıl biçimine göre dönüştürmek istiyor. Tabii ilk satırdaki gibi 12 rakamından büyük bir ay olmadığından hata veriyor. Aynı sıkıntı “Ship Date” sütununda da başımıza gelecek. (Eğer işletim sisteminizde tarih ayarları Amerikan/İngiliz standardına göre ayarlandı ise Avrupa tarih standardına göre girilmiş tarihlerin ayarlanmasında da hata alırsınız)
Şimdi bu hatayı nasıl gidereceğiz, tarih formatı Amerikan formatı olduğunu söylemiştim. Bizim burada da işletim sisteminde ayarlı olan tarih biçimi yerine Amerikan standardını kullanmamız gerek. Önce bir önceki tür değişikliği adımını silin. Sonrasında sütunun solundaki simgeye tekrar basarak gelen menünün en altında yer alan “Yerel ayarları kullanarak” simgesine tıklıyoruz. Yeni bir pencere açılacak, burada üstteki kutuya tıklayarak Tarih seçeneğini seçin, altındaki kutuda ise “İngilizce (A.B.D.)” seçeneğini seçin. Artık tarihler düzgün bir biçimlendirmeye sahip olacaktır. Aynı işlemi “Ship Date” sütununa da uygulayın.
Aynı sorun “Unit Price”, “Unit Cost”, “Total Revenue”, “Total Cost” ve “Total Profit” sütunlarında da oluşacaktır. Çünkü buradaki rakamlar ondalık olmasına rağmen ondalık işareti olarak bizim kullandığımız “,” yerine “.” kullanıyor. Şimdi bunları da Yerel Ayar Kullanarak seçeneği ile “Para Birimi”, “İngilizce (A.B.D.)” olarak değiştirin. Tabloyu kapatıp yüklediğinizde artık ilgili sütunların, dosyadaki gerçek biçimlerine sahip olduğunu göreceksiniz.
Bu aşamada isterseniz nihai tablodaki ilgili sütunlara siz de istediğiniz biçimlendirmeyi uygulayabilirsiniz. Ben maddi değerlerin olduğu sütunları Para Birimi, $ İngilizce (A.B.D.) olarak değiştirdim. Sonuç aşağıdaki gibi olacaktır.
Gördüğünüz gibi Power Query ile csv, metin belgesi gibi başka kaynaklardan gelen verileri de Excel’e hızlıca aktarabiliyorsunuz.
Tabii siz Excel ile bu dosyaları doğrudan açabilirsiniz, ancak bu durumda farklı biçimlendirme ile gelen tarih ve ondalıklı sayıları istediğiniz biçime getirebilmek için oldukça fazla uğraşmak zorunda kalırsınız. Bu değerleri metin olarak ele almak ve sonrasında metin formülleri ile tarihleri bölmek ve sonrasında tekrar uygun biçimde tarihe dönüştürmek oldukça zaman alıcı bir işlemdir.
Bir de bu işlemi sürekli yaptığınızı düşünün. Siz bu sorguyu kopyalayabilirsiniz. Power Query Düzenleyicinideki “Kaynak” adımına çift tıklayarak farklı bir dosyayı seçebilirsiniz. Ve tüm bu adımları tekrar oluşturmadan yaptığınız işlemleri seçtiğiniz bu yeni dosyaya uygulanmasını sağlayabilirsiniz.