Excel'in Al ve Dönüştür İşlevselliğini Keşfetmek

Yönetici Özeti

Bu veri gölleri ve petabayt ölçekli veritabanları çağında, hala CSV, metin ve Excel dosyaları biçiminde veri almam şaşırtıcı. Günümüz analitiği, makine öğrenimi algoritmalarındaki en son gelişmelere odaklanırken, günlük veri analizi angaryası, farklı veri türlerini bulma, derleme ve tartışmaya yönelik manuel bir süreç olmaya devam ediyor.

Mali analist için, veriler genellikle bir Excel elektronik tablosu olarak gelir, ancak aynı sıklıkla, bir CSV'ye veri dökümü veya bir SQL veritabanına bir sorgudur. Bazen veriler kafa karıştırıcı bir düzende düzenlenir veya analiz için gerekli tüm bileşenlere sahip değildir. Bu verileri temizlemek için harcanan zaman, analist için boşa harcanan değerli bir zamandır, ancak bazen bu görev, tolere edilmesi gereken bir kötülük olarak kabul edilir.

Get & Transform Ne Yapar?

Bu yaygın soruna yönelik bir çözüm aslında oldukça erişilebilir: Excel ve Power BI, Get & Transform (önceden Power Query olarak biliniyordu) adlı, çok az kullanıcının farkında olduğu bir dizi veri dönüştürme aracına sahiptir. Gömülü ayıklama, dönüştürme ve yükleme (ETL) işlevselliğini kullanmak, finansal analistlerin veri kaynaklarına sorunsuz bir şekilde bağlanmasını ve öngörülere daha hızlı ulaşmasını sağlar.

Verileri Excel'e veya Power BI'a yüklemek üzere hazırlarken, genellikle verilerde bazı dönüşümler yapmamız gerekir. Bazı veri işleme örnekleri şunları içerir:

• Sütunları kaldırmak,
• Verileri filtrelemek,
• Verilerin gruplandırılması,
• Verilerin döndürülmesi/döndürülmesi,
• Dizeleri alt dizelere bölme,
• Dizelerden anahtar kelimeleri çıkarmak,
• Başka bir tablodan satır ekleme ve
• İki boyut tablosunun birleştirilmesi.

Aşağıdaki şemada, Get & Transform'un verileri yüklenmeden önce bu sıkıcı ön işleme görevini yerine getirdiğini görüyoruz.

Neden Al ve Dönüştür'ü Kullanmalısınız?

Get & Transform'un nasıl kullanılacağını öğrenmek neden değerlidir? Pekala, bu işlevi kişisel olarak ne için kullandığıma baktığımda, bana aşağıdakiler için dövülebilir bir araç seti sundu:

• Tüm bir metin dosyası klasörünü tek bir veri tablosuna yükleme
• Dışa aktarılan muhasebe dosyalarını sindirilebilir bir düzene dönüştürme
• Milyonlarca satış satırını doğrudan Power Pivot'a yükleme
• Günlük verileri Excel'e aktarmadan önce yönetilebilir aylık sonuçlar halinde gruplama
• Eşleşen sütunlara katılarak başka bir tablodan veri ekleme

Genel olarak, yeni veriler aldığımda, Power Pivot'a yüklemeden önce Get & Transform kullanarak keşfedeceğim. Bu, hangi dönüşümlerin gerekli olabileceğini görmeme ve analiz için bir çerçeve formüle etmek için veriler üzerinde hızlı bir şekilde bazı pivotlar ve gruplamalar gerçekleştirmeme izin veriyor. Çoğu durumda, bu aşamada daha fazla veriye ihtiyacım olduğunu veya veri sorunları olduğunu fark edeceğim. Excel tabanlı bir platform kullanarak, bu veri anormalliklerini bulmak için veri kaynağımla hızlı bir şekilde yineleme yapabilirim.

Sonuç olarak, Excel'de kalma veya veri analizini başka bir platforma taşıma kararı, hedef kitleye ve analizin tekrarlanabilirliğine ve dağıtımına bağlı olacaktır. Müşterilerim yalnızca Excel kullanıyorsa, verileri yüklemek için hemen hemen her zaman Al ve Dönüştür'ü, analizi gerçekleştirmek için Power Pivot'u ve PivotTable'ları ve grafikleri üretmek için Excel'i kullanacağım. Müşteriye, hepsi Excel'de barındırıldığından, bu sorunsuz hissedecektir.

Ancak, eğer müşterim:

1. Başka bir görselleştirme aracı kullanmak istiyor,
2. Verileri yenileyecek birden fazla kullanıcısı varsa veya
3. Makine öğrenimi modellerini kullanma ihtiyacı,

Ardından Get & Transform'u yalnızca ilk veri araştırması için kullanacağım ve ardından ağır yükü R'ye taşıyacağım.

Excel veya Power BI'da Al ve Dönüştür'e Nasıl Erişilir

Excel'in önceki sürümlerinde, Power Query, ETL işlevlerine yardımcı olmak için yüklenebilen bir eklentiydi. Ancak Excel 2016 ve Power BI'da bu araçlar daha sıkı bir şekilde entegre edilmiştir. Excel 2016'da bunlara Veri sekmesinden ve ardından Verileri Al ve Dönüştür bölümünden erişilebilir.

Power BI'da işlevsellik, Giriş sekmesinin Dış Veri bölümünde bulunur.

Bu yazıda örneklerim Power BI'da yer alıyor ancak arayüz Excel'inkiyle neredeyse aynı. Öğreticinin her iki kullanıcı türü için de anlamlı olması için, ortaya çıktıklarında farklılıkları işaret edeceğim.

1. CSV Dosyalarını Yükleme

Bu eğiticiye yardımcı olmak için, dış mekan malzemeleri ve kıyafetleri satan kurgusal bir perakendeci için birkaç satış verisi örneği oluşturdum. Bu örneklerin her birinde, veriler, gerçekçi veri dökümü yöntemlerini göstermek için farklı şekillerde üretilecektir.

İlk örnek olarak, bir CSV dosyasına büyük bir veri dökümü olarak sunulan verileri göreceğiz. Karmaşık faktör, verilerin çeşitli mağazaları temsil eden birden çok sütunla sunulmasıdır. İdeal olarak, verileri içe aktarmak ve daha kullanışlı bir düzene dönüştürmek istiyoruz.

Aşağıda, ham CSV'nin nasıl göründüğünün bir ekran görüntüsü verilmiştir:

Bunu neden değiştirmek isteyelim ki? Bu uygulamalarda mümkün olan ilişki yeteneklerinden yararlanmak. Bu oyunu tartışmanın devamında göreceğiz.

Şimdilik verileri “daha ​​geniş ve daha kısa” değil, “daha ​​dar ve uzun” bir yapı olarak görmemiz gerektiğini varsayalım. İlk adım, CSV'yi yüklemektir; sonra, verileri "çözmeye" başlayacağız.

Gördüğünüz gibi, verilerin nihai yapısı, ilk verilere göre daha dar ve çok daha uzun. Diğer bir nokta ise, biz farklı eylemlere tıklarken, sağ taraftaki araç, sorguyu oluşturmak için kullanılan uygulamalı adımların bir listesini oluşturuyor. Daha sonra tekrar gözden geçirileceği için bunun arka planda devam ettiğini anlamak önemlidir.

Al ve Dönüştür, çoğunlukla Power BI ve Excel arasında benzer şekilde görünür ve davranır. Ancak Excel'de Kapat ve Yükle'ye tıkladıktan sonra ek bir bilgi istemi vardır. Aşağıdaki şekilde, verileri yüklemek isteyip istemediğimiz arasında geçiş yapabiliriz:

1. Excel'de bir tablo,
2. Verilere karşı oluşturulan bir PivotTable,
3. Verilere karşı oluşturulan bir PivotChart veya
4. “Yalnızca Bağlantı Oluşturun.”

Ayrıca, bu verileri Veri Modeline Ekleme veya Ekleme seçeneği de bize sunulmaktadır. Bu kutunun işaretlenmesi, verileri bir Power Pivot tablosuna yükler. Power Pivot'ta verileri analiz edeceksek, Yalnızca Bağlantı Oluştur'u seçmenizi ve ardından Bu verileri Veri Modeline Ekle seçeneğinin seçili olduğundan emin olmanızı öneririm. Veriler Excel satır sınırı içindeyse ve analizimizi Excel'de yapmayı tercih ediyorsak, o zaman Tablo'yu seçmeniz yeterlidir.

Bir sonraki klipte, verileri uzun ve sıska olacak şekilde biçimlendirmemizin sebebinin satışları sadece mağaza bazında değil bölge ve eyalet bazında da analiz edebilmemiz olduğunu göreceğiz. Bu görevi gerçekleştirmek için her mağazayı bir bölge ve eyaletle eşleyen bir tablo içe aktaracağız. Aşağıda bu farklı gruplamalara göre satışları gösteren raporları hızlıca oluşturabileceğimizi göreceğiz.

Excel'de veya Power BI'da bu tür bir veri dönüştürme yeteneğinin, aşağıdakiler gibi dinamik veri gruplamalarına sahip olduğumuz herhangi bir duruma nasıl güçlü bir şekilde uygulanabileceğini hayal edebilirsiniz:

• Günlük verileri haftalara, aylara ve çeyreklere toplamak;
• Satış personelinin departmanlara ve bölgelere göre gruplandırılması; veya
• SKU'ları ürün türleriyle eşleme.

Bu makale CSV ve diğer Excel dosyalarını ele alırken, Get & Transform çok çeşitli veri türlerini ele alır. Bir sorgu oluşturulduktan sonra, veriler değiştikçe zaman içinde yenilenebilir.

2. Metin Dizelerini Kullanma

Get & Transform'un dizeleri manipüle etme yeteneğini göstermek için, bir firmanın defteri kebirinden (GL) muhasebe işlemlerini gösteren bir metin dosyasını taklit eden başka bir veri seti oluşturdum.

Hesap numarası ve adın aynı dizede nasıl göründüğüne dikkat edin? Power BI'da hesap numarasını ve adını zahmetsizce ayrı alanlara ayrıştırabiliriz.

Bu videoda, sütunu böldükten sonra, aracın Hesap alanının yeni sol tarafının bir sayı olması gerektiğini tahmin ettiğini ve “Changed Type1” adımını oluşturduğunu görebilirsiniz. Sonuçta bu alanı string olarak istediğimiz için devam edip uygulanan adımların altındaki adımı manuel olarak silebiliriz.

Daha sonra, aynı verileri alıyoruz ve hesap kategorilerine eşlemeler içeren bir hesap planı oluşturuyoruz.

Neden birkaç hesap numarasını eşlemek için tüm bu adımları atalım? Gerçek bir defteri kebir yüzlerce hatta binlerce hesap olabilir. Bu hızlı eşleme sorgusu, gösterdiğimiz gibi, ek bir çalışma olmaksızın bu düzeye ölçeklenir.

3. Farklı Veri Kaynaklarıyla Çalışmak

Get & Transform, birçok farklı veri kaynağını destekler. Kapsamlı bir liste olmasa da, aşağıda bazı örnekler verilmiştir:

Şahsen, yukarıdaki listedeki bağlantıların sadece yarısını denedim. Kullandığım bağlayıcıların her biri oldukça sağlamdı; Ham verilerden içgörülere çok fazla iş yükü olmadan ulaştım. Aynı derecede önemli olarak, farklı veri kaynakları arasında bir doğrulayıcı görevi görerek nihai çıktıların normalleştirilmiş bir kalite kontrol seviyesine sahip olmasını sağlar.

4. Kodu M Dili ile Kişiselleştirme

Arka planda Al ve Dönüştür, araçtaki bir düğmeye her tıkladığımızda veya bir seçim yaptığımızda kod üretiyor. Aşağıda, oluşturduğumuz hesap eşleme sorgusu için koda nasıl erişeceğinize ilişkin bir örnek verilmiştir:

Kod, temel kullanım durumları için otomatik olarak oluşturulan M adlı işlevsel bir dil kullanır. Ancak, daha karmaşık veri tartışmaları için kendi kodumuzu düzenleyebilir ve yazabiliriz. Çoğu durumda, bu kodda yalnızca küçük değişiklikler yapacağım. Daha karmaşık dönüşümlerde, kodun çoğunu sıfırdan geçici tablolara veya daha karmaşık birleştirmeler gerçekleştirmek için yazabilirim.

Al ve Dönüştürmenin Sınırları

Bir milyondan fazla satırı dışa aktarmayı denediğinizde Excel sınırlarına ulaşma eğilimindedir. Get & Transform ile milyonlarca satırı dönüştürdüğüm durumlarda, gruplanmamış satırları göndermenin tek yolu sıkıcı hack'ler veya geçici çözümler kullanmaktır. Ayrıca, özellikle birden çok veri kaynağı ve birleştirme kullanıyorsanız, Al ve Dönüştür sorgularının birden çok kullanıcıya dağıtılmasının kararsız olabileceğini de buldum. Bu durumlarda, yinelenebilir veri tartışmasını dağıtmak için her zaman R kullanacağım. Son olarak, Excel daha gelişmiş veri modelleme için oluşturulmamıştır. Doğrusal regresyonları oldukça hızlı bir şekilde gerçekleştirebilirsiniz, ancak bunun ötesinde daha titiz bir platform kullanmanız gerekecektir.

Tüm bunları söyledikten sonra, müşterilerimin çoğunun en rahat olduğu şeyin Excel olduğunu görüyorum. Excel, bir finansal analistin cephaneliğindeki en önemli araçtır. Al ve Dönüştür işlevini dahil ederek Excel ve Power BI, kabul edebilecekleri veri kaynakları yelpazesi sayesinde daha da güçlü hale gelir.