Excel Power Query öğreticisi‌ (al ve dönüştür)

Verilerle çok çalışan kişilerden biriyseniz, herhangi biri olabilirsiniz ( muhasebeci , İK, veri analisti vb.), PowerQuery sizin güçlü aracınız olabilir.

Konuyu kısa keseyim, Power Query, öğrenmeniz gereken gelişmiş Excel becerilerinden biridir ve bu eğitimde PowerQuery’yi ayrıntılı olarak keşfedecek ve onunla verileri nasıl dönüştüreceğinizi öğreneceksiniz.

Başlayalım.

Excel Güç Sorgusu Nedir?

Power Query, ETL için kullanabileceğiniz bir Excel eklentisidir. Bu, farklı kaynaklardan veri ayıklayabileceğiniz , dönüştürebileceğiniz ve ardından elektronik tabloya yükleyebileceğiniz anlamına gelir. POWER QUERY’nin bir veri temizleme makinesi olduğunu söyleyebilirsiniz çünkü verileri dönüştürmek için tüm seçeneklere sahiptir. Gerçek zamanlıdır ve attığınız her adımı kaydeder.

Power Query’yi (Avantajlar) neden kullanmalısınız?

Aklınızda bu soru varsa işte size cevabım:

• Farklı veri kaynakları : CSV , TXT, JSON vb. gibi farklı veri kaynaklarından verileri güçlü bir sorgu düzenleyiciye yükleyebilirsiniz.
• Verileri kolayca dönüştürün: Normalde veri dönüştürmeleri için formüller ve pivot tablolar kullanırsınız ancak POWER QUERY ile yalnızca birkaç tıklamayla çok şey yapabilirsiniz.
• Gerçek zamanlı: Bir sorguyu bir kez yazdığınızda, verilerde her değişiklik olduğunda onu yenileyebilirsiniz; bu, güncellediğiniz yeni verileri dönüştürecektir.

Bir örnek paylaşayım:

100 şehrin verilerini içeren 100 Excel dosyanız olduğunu ve şimdi patronunuzun bu 100 dosyadaki tüm verileri içeren bir rapor oluşturmanızı istediğini düşünün. Tamam, her dosyayı manuel olarak açmaya ve bu dosyalardan verileri kopyalayıp yapıştırmaya karar verirseniz ve bunun için en az bir saate ihtiyacınız var.

Ancak Power Query ile bunu birkaç dakika içinde yapabilirsiniz. Heyecanlı mısın? İYİ.

Bu eğitimin ilerleyen kısımlarında Power Query’nin nasıl kullanılacağını birçok örnekle öğreneceksiniz, ancak önce konseptini anlamanız gerekiyor.

Güç talebi kavramı

Güç sorgusunu öğrenmek için 3 adımda çalışan konseptini anlamanız gerekir:

1. Verileri alın

Güç sorgusu, web, CSV, metin dosyaları, tek bir klasörden birden fazla çalışma kitabı ve veri depolayabileceğimiz diğer birçok kaynak gibi farklı kaynaklardan veri almanızı sağlar.

2. Verileri dönüştürün

Güç sorgusuna veri aldıktan sonra, onu dönüştürmek ve temizlemek için kullanabileceğiniz çok sayıda seçeneğiniz vardır. Gerçekleştirdiğiniz tüm adımlar için (birbiri ardına sırayla) sorgular oluşturur.

3. Verileri yükleyin

Gelişmiş Sorgu Düzenleyicisi’nden dönüştürülen verileri çalışma sayfasına yükleyebilir veya doğrudan PivotTable veya PivotChart oluşturabilir veya yalnızca veri bağlantısı oluşturabilirsiniz.

Power Query nerede (Nasıl yüklenir)?

Aşağıda Microsoft Excel’in farklı sürümlerine Power Query Access’in nasıl yükleneceğini görebilirsiniz.

Excel 2007

Excel 2007 kullanıyorsanız, PQ’nun bu sürümde mevcut olmamasından dolayı üzgünüz, bu nedenle Excel’in latest version (Office 365 için Excel, Excel 2019, Excel 2016, Excel 2013, Excel 2010) yükseltmeniz gerekir.

Excel 2010 ve Excel 2013

2010 ve 2013 için bu bağlantıdan indirebileceğiniz bir eklentiyi ayrıca yüklemeniz gerekir ve yükledikten sonra Excel şeridinde aşağıdaki gibi yeni bir sekme göreceksiniz:

• Öncelikle eklentiyi buradan (Microsoft resmi web sitesi) indirin.
• Dosya indirildikten sonra açın ve talimatları izleyin.
• Bundan sonra Excel şeridinizde otomatik olarak “Power Query” sekmesini göreceksiniz.

Eğer bir şekilde bu “GÜÇ SORGULAMA” sekmesi görünmüyorsa endişelenmenize gerek yok. COM Eklentileri seçeneğini kullanarak ekleyebilirsiniz.

• Dosya sekmesi ➜ Seçenekler ➜ Eklentiler’e gidin.

• “Eklenti” seçeneklerinde “COM Eklentileri”ni seçin ve DEVAM’a tıklayın.
• Bundan sonra “Excel için Microsoft Power Query” kutusunu işaretleyin.

• Sonunda Tamam’ı tıklayın.

Excel 2016, 2019, Ofis 365

Excel 2016, Excel 2019 kullanıyorsanız veya bir OFFICE 365 aboneliğiniz varsa, bu zaten Veri sekmesinde “GET & TRANSFORM” adlı bir grup olarak mevcuttur (bu adı beğendim, ya siz?).

Excel Mac’i

Mac’te Excel kullanıyorsanız, korkarım bunun için bir güç sorgusu eklentisi yok ve yalnızca mevcut bir sorguyu yenileyebilirsiniz , ancak yeni bir sorgu oluşturamaz ve hatta bir sorguyu düzenleyemezsiniz ( LINK ).

Güç Sorgu Düzenleyicisi

Power Query’nin, verileri alabileceğiniz, sorgu oluşturmak için tüm adımları gerçekleştirebileceğiniz ve ardından bunları elektronik tabloya yükleyebileceğiniz kendi düzenleyicisi vardır. Power Query Düzenleyicisi’ni açmak için Veri sekmesine gitmeniz ve Al ve Dönüştür ➜ Veri Al ➜ Power Query Düzenleyicisini Başlat’a gitmeniz gerekir.

Aşağıda, açtığınızda alacağınız düzenleyicinin ilk önizlemesi bulunmaktadır.

Şimdi her bölümü ayrıntılı olarak inceleyelim:

1. Şerit

Mevcut tüm sekmelere bakalım:

• Dosya: Dosya sekmesinden verileri yükleyebilir, düzenleyiciyi kaldırabilir ve sorgu parametrelerini açabilirsiniz.
• Ana Sayfa : GİRİŞ sekmesinde, sütunlar ve satırlar gibi yüklenen verileri yönetme, silme ve taşıma seçenekleriniz vardır.
• Dönüştürme : Bu sekme, sütunları birleştirme, devriği değiştirme vb. gibi verileri dönüştürmek ve temizlemek için ihtiyacınız olan tüm seçenekleri içerir.
• Sütun Ekle : Burada gelişmiş düzenleyicide sahip olduğunuz verilere yeni sütunlar ekleme seçeneğiniz vardır.
• Görünüm : Bu sekmeden Gelişmiş Sorgu Düzenleyicisi görünümünde ve yüklenen verilerde değişiklik yapabilirsiniz.

2. Uygulanan adımlar

Düzenleyicinin sağ tarafında, sorgu adını ve sırayla uygulanan tüm adımları içeren bir sorgu ayarları bölmesi bulunur.

Bir adıma sağ tıkladığınızda, yeniden adlandırma, silme, düzenleme, yukarı veya aşağı taşıma vb. gibi yapabileceğiniz seçeneklerin bir listesi bulunur. ve bir adıma tıkladığınızda editör sizi o adımda yapılan dönüşüme götürecektir.

Aşağıya bakın, toplamda beş adımı da uyguladınız ve 4. adıma tıkladığımda beni sütun adlarının değişmediği dördüncü adımdaki dönüşüme götürüyor.

3. Sorular

Sol taraftaki sorgular bölmesi, şu anda çalışma kitabında bulunan tüm sorguları listeler. Temel olarak tüm sorguları yönetebileceğiniz bir yerdir.

Bir sorgu adına sağ tıkladığınızda kullanabileceğiniz tüm seçenekleri (kopyalama, silme, çoğaltma vb.) görebilirsiniz.

Ayrıca sorgular bölmesindeki boş alana sağ tıklayıp ardından veri kaynağı seçeneğini belirleyerek de yeni bir sorgu oluşturabilirsiniz.

4. Formül çubuğu

Dediğim gibi editörde her adım uyguladığınızda o adıma ait M kodu üretiyor ve bu kodu formül çubuğunda görebiliyorsunuz. Kodu düzenlemek için formül çubuğuna tıklamanız yeterlidir.

M kodunu nasıl kullanacağınızı öğrendikten sonra, kodu yazarak ve özel bir adım girmek için “FX” düğmesine tıklayarak da bir adım oluşturabilirsiniz.

5. Verilere Genel Bakış

Veri önizleme alanı bir Excel elektronik tablosuna benzer ancak bir hücreyi veya verileri doğrudan düzenleyebileceğiniz normal bir elektronik tablodan biraz farklıdır. Düzenleyiciye veri yüklediğinizde (bunu birazdan yapacağız), başlıklarıyla birlikte tüm sütunları sütun adlarıyla birlikte ve ardından verileri içeren satırları görüntüler.

Her sütunun üst kısmında, sütunun verilerinin veri türünü görebilirsiniz. Düzenleyiciye veri yüklediğinizde Gelişmiş Sorgu, doğru veri türünü (neredeyse her zaman) her sütuna otomatik olarak uygular.

Sütuna uygulanan veri türünü değiştirmek için sütun başlığının sol üst kısmındaki düğmeyi tıklayabilirsiniz. Çizim yapabileceğiniz tüm veri türlerinin bir listesini içerir.

Sütun başlığının sol tarafında, sütun değerlerini filtrelemek için kullanabileceğiniz filtre düğmesi bulunur. Not: Bir sütunun değerlerini filtrelediğinizde, gelişmiş sorgu bunu tek bir adım olarak değerlendirir ve Uygulanan Adımlar’da listeler.

Sütun başlığına sağ tıklarsanız, verileri dönüştürmek ve seçeneklerden birini ve uygulanan süreçte bir adım olarak sakladığı PQ’yu kullanmak için kullanabileceğiniz seçeneklerin listesini içeren bir menü olduğunu görebilirsiniz. Olumsuz.

Power Query için veri kaynakları

Power Query’nin en iyi yanı, birden çok kaynaktan veri alıp bu verileri dönüştürüp elektronik tabloya yükleme olanağına sahip olmanızdır. GET & TRANSFORM’da Veri Al’ı tıklattığınızda, düzenleyiciye yükleyebileceğiniz veri kaynaklarının tam listesini görebilirsiniz.

Şimdi bazı veri kaynaklarına bakalım:

• Tablodan/Aralıktan : Bu seçenekle, verileri doğrudan etkin çalışma sayfasından Gelişmiş Sorgu Düzenleyicisi’ne yükleyebilirsiniz.
• Çalışma kitabından : Bilgisayarınızdaki başka bir çalışma kitabından. Açık bir iletişim kutusu kullanarak bu dosyayı bulmanız yeterlidir; bu dosyadaki verileri otomatik olarak alacaktır.
• Metin/CSV’den : Metin dosyasından veya virgülle ayrılmış dosyadan veri alın, ardından bunu elektronik tabloya yükleyebilirsiniz.
• Klasörden : Klasördeki tüm dosyaları alır ve güçlü sorgu düzenleyicideki verileri destekler. (Şuna bakın: Excel dosyalarını tek bir klasörden birleştirme).
• Web’den : Bu seçenekle bir web adresinden veri alırsınız, web’de saklanan bir dosyanız olduğunu veya verileri almanız gereken bir web sayfanız olduğunu düşünün.

Power Query Düzenleyicisi’ne veri yükleme

Şimdi verileri güçlü sorgu düzenleyicisine nasıl yükleyeceğinizi öğrenelim. Burada öğrenci adlarının ve puanlarının bir listesi bulunmaktadır ( LINK ).

Verileri doğrudan e-tablodan yükleyeceğiniz için önce dosyayı açmanız ve ardından aşağıdaki adımları uygulamanız gerekir:

• Öncelikle verilere bir Excel tablosu uygulayın (siz yapmasanız bile Excel, verileri PQ düzenleyicisine yüklemeden önce bunu sizin için yapacaktır).
• Şimdi tablodan bir hücre seçin ve “Tablodan/Aralıktan” (Veri Al ve Dönüştür sekmesi) tıklayın.

• Düğmeye tıkladığınızda Excel, bir Excel tablosunun uygulanacağı veri aralığını onaylar.

• Bu noktada Power Query Düzenleyicisi’nde verileriniz var ve aşağıdaki gibi görünüyor.

• Burada görebilirsin:
  • Formül çubuğunda PQ, düzenleyiciye yeni yüklediğiniz tablo için M kodunu oluşturdu.
  • Düzenleyicinin sol tarafında, sorgu listesinin bulunduğu sorgular bölmesi bulunur.
  • Sağ tarafta, sorgu ayarlarında tüm adımların listelendiği “Uygulanan Adımlar” adlı bölüm bulunmaktadır. Not: Herhangi bir “Modified Type” işlemi yapmadığınızı düşünmelisiniz ancak burada “Modified Type” diye bir adım var. POWER QUERY’nin ZEKÂSINI anlatayım, editöre veri yüklediğinizde otomatik olarak kontrol eder ve tüm sütunlar için doğru veri türlerini uygular.

Power Query Örnekleri (İpuçları ve Püf Noktaları)

Normalde Excel’deki işlevsel formüllerle yaptığınız bazı temel görevleri nasıl yapacağınızı öğrenebilirsiniz, ancak PowerQuery ile bunu yalnızca birkaç tıklamayla yapabilirsiniz:

1. Değerleri değiştirin

Bir değerler listeniz var ve bir veya daha fazla değeri başka bir şeyle değiştirmek istiyorsunuz. Power Query’nin yardımıyla kısa sürede bir sorgu oluşturabilir ve bu değerleri değiştirebilirsiniz.

Aşağıdaki listede “Puneet” adımı “Punit” ile değiştirmeniz gerekiyor.

• Öncelikle Gelişmiş Sorgu Düzenleyicisi’ndeki listeyi düzenleyin.
• Bundan sonra Power Query Düzenleyicisi’nde “Dönüştürme Sekmesi”ne gidin ve “Değerleri Değiştir”e tıklayın.

• Şimdi “Bulunacak değer”e “Puneet” yazın ve “Şununla değiştir”e “Punit” girin ve ardından Tamam’a tıklayın.

• Tamam’a tıkladığınızda tüm değerler yeni değerlerle değiştirilir ve şimdi verileri e-tabloya yüklemek için “Kapat ve Yükle”ye tıklayın.

2. Verileri sıralayın

Normal sıralamada olduğu gibi, PowerQuery’yi kullanarak verileri sıralayabilirsiniz; ben de yukarıdaki örnekte kullandığınız ad listesinin aynısını kullanıyorum.

• İlk olarak verileri güçlü sorgu düzenleyicisine yükleyin.
• Giriş sekmesinde iki sıralama düğmeniz vardır (Artan ve Azalan).
• Sıralamak için bu düğmelerden birine tıklayın.

3. Sütunları silin

Diyelim ki bir yerlerde bazı verileriniz var ve bu verilerden bazı sütunları kaldırmanız gerekiyor. Mesele şu ki, her yeni veri eklediğinizde bu sütunları silmeniz gerekiyor, değil mi? Ancak güç sorgusu bunu halledebilir.

• Silmek istediğiniz sütunu veya birden çok sütunu seçin.

• Şimdi sağ tıklayın ve “Sil”i seçin.

Hızlı İpucu: Seçili olmayan tüm sütunları silebileceğiniz “Diğer sütunları kaldır” seçeneği de vardır.

4. Sütunları böl

Tıpkı sütuna metin seçeneği gibi, güç sorgusunda da “Sütunu Böl” seçeneğiniz vardır. Size nasıl çalıştığını anlatayım.

• Sütunu seçin ve Ana Sayfa sekmesine gidin ➜ Dönüştür ➜ Sütunu böl ➜ Sınırlayıcıya göre.

• Açılır listeden özel olanı seçin ve içine “–” girin.
• Artık burada bir sütunu bölmek için üç farklı seçeneğiniz var.
  • En soldaki sınırlayıcı
  • En sağdaki sınırlayıcı
  • Sınırlayıcının her oluşumu

Bir hücrede yalnızca bir sınırlayıcınız varsa üçü de aynı şekilde çalışacaktır ancak birden fazla sınırlayıcınız varsa buna göre seçim yapmalısınız.

5. Bir sütunu yeniden adlandırın

Bir sütunu sağ tıklayıp ardından “Yeniden Adlandır” seçeneğine tıklayarak yeniden adlandırabilirsiniz.

Kısa ipucu : Diyelim ki bir sütunu yeniden adlandırmak için bir sorgunuz var ve başka biri onu yanlışlıkla yeniden adlandırdı. Bu adı tek bir tıklamayla geri yükleyebilirsiniz.

6. Sütunu çoğaltın

Power Query’de yinelenen bir sütun oluşturmak için basit bir seçenek vardır. Çoğaltılmış sütun oluşturmak istediğiniz sütuna sağ tıklayın ve ardından “Sütunu Çoğalt” seçeneğine tıklayın.

7. Sütun veya satırın yerini değiştir

Güç sorgusunda aktarım çocuk oyuncağıdır. Evet, sadece tek bir tıklama.

• Verileri Gelişmiş Sorgu Düzenleyicisine yükledikten sonra tek yapmanız gereken sütunları veya satırları seçmektir.
• Dönüştürme sekmesine gidin ➜ Tablo ➜ Transpoze.

8. Hataları değiştirin/kaldırın

Normalde Excel’deki hataları değiştirmek veya kaldırmak için Bul ve Değiştir seçeneğini veya VBA kodunu kullanabilirsiniz. Ancak PowerQuery’de bu çok daha kolaydır. Hatalarınızın olduğu aşağıdaki sütuna bakın; bunları kaldırabilir ve değiştirebilirsiniz.

Sütuna sağ tıkladığınızda her iki seçeneğe de sahip olacaksınız.

• Hataları değiştir
• Hataları kaldır

9. Veri türünü değiştirin

Bir sütunda verileriniz var ancak bunlar doğru formatta değil. Yani her seferinde formatını değiştirmeniz gerekir.

• İlk olarak verileri güçlü sorgu düzenleyicide düzenleyin.
• Bundan sonra sütunu seçin ve Dönüştür sekmesine gidin.
• Şimdi Veri Türü’nden tür olarak “Tarih”i seçin.

10. Örneklerden sütun ekleyin

Gelişmiş sorguda, aslında geçerli sütunla ilgili bir örnek olmayan örnek bir sütun ekleme seçeneği vardır.

Sana bir örnek vereyim:

Bir tarih sütunundan gün adlarına ihtiyacınız olduğunu düşünün. Formül ya da başka bir seçenek kullanmak yerine “Örneklerden Sütun Ekle” seçeneğini kullanabilirsiniz.

Bunu nasıl yapacağınız aşağıda açıklanmıştır:

• Bir sütuna sağ tıklayın ve “Örneklerden Sütun Ekle”ye tıklayın.

• Burada boş bir sütun alacaksınız. Ekleyebileceğiniz değerlerin bir listesini almak için sütundaki ilk hücreye tıklayın.

• “Tarihten itibaren haftanın gününün adı” seçeneğini seçin ve Tamam’a tıklayın.

Boom! yeni sütununuz burada.

11. Kutuları değiştirin

PowerQuery’de metnin büyük/küçük harf durumunu değiştirmek için aşağıdaki seçeneklere sahipsiniz.

• Minik
• Üst kutu
• Her kelimeyi büyük harfle yaz

Bunu, bir sütuna sağ tıklayıp yukarıdaki üç seçenekten birini seçerek yapabilirsiniz. Veya Dönüştür sekmesine gidin ➜ Metin Sütunu ➜ Biçim.

12. Kırpın ve temizleyin

Verileri temizlemek veya istenmeyen boşlukları kaldırmak için PowerQuery’deki TRIM ve CLEAN seçeneklerini kullanabilirsiniz. Adımlar basit:

• Bir sütuna sağ tıklayın veya birden fazla sütununuz varsa tüm sütunları seçin.
• Dönüştür sekmesine gidin ➜ Metin Sütunu ➜ Biçim.
  1. TRIM: Hücrenin sonundaki ve başlangıcındaki beyaz boşlukları kaldırmak için.
  2. TEMİZLE: yazdırılmayan karakterleri hücreden kaldırmak için.

13. Bir önek/sonek ekleyin

Yani bir değerler listeniz var ve bu listeden her hücreye bir önek/sonek eklemek istiyorsunuz. Excel’de birleştirme yöntemini kullanabilirsiniz ancak PowerQuery’de her ikisi için de kullanımı basit bir seçenek vardır.

• Öncelikle önek/sonek eklemeniz gereken sütunu seçin.
• Daha sonra Dönüştür sekmesine gidin ➜ Metin Sütunu ➜ Biçim ➜ Önek Ekle/Sonek Ekle.

• Seçeneklerden herhangi birine tıkladığınızda, metin girmek için bir iletişim kutusu açılır.

• Ve metni girdikten sonra Tamam’ı tıklayın.

14. Değerleri çıkarın

Eğer bir formül meraklısıysanız, bir hücreden metin veya sayı çıkarmanın farklı işlevleri birleştirmeyi gerektirdiği konusunda benimle aynı fikirde olduğunuzdan eminim. Ancak PowerQuery bu sorunların çoğunu çözmüştür. Bir hücreden değer çıkarmanın yedi yolu vardır.

15. Yalnızca tarih veya saat

Çoğu zaman tarih ve saatin her ikisi de tek bir hücrede bulunur, ancak bunlardan birine ihtiyacınız vardır.

• Birleşik tarih ve saatin bulunduğu sütunu seçin.
• Eğer istersen:
  • Tarih : Sağ tıklayın ➜ Dönüştür ➜ Yalnızca tarih.
  • Zaman : ➜ Dönüştür ➜ Yalnızca Zaman’a sağ tıklayın.

16. Tarih ve saati birleştirin

Artık tarih ve saati nasıl ayıracağınızı biliyorsunuz. Ancak daha sonra bunları nasıl birleştireceğinizi bilmeniz gerekir.

• Öncelikle tarih sütununu seçin ve “Yalnızca tarih” seçeneğine tıklayın.
• Daha sonra her iki sütunu da (Tarih ve Saat) seçip Dönüştürme sekmesine gidin ve “Tarih ve Saat Sütunu” grubundan Tarih’e gidin ve “Tarih ve Saati Birleştir” seçeneğine tıklayın.

17. Yuvarlatılmış sayılar

Sayıları yuvarlamak için aşağıdaki seçenekleri kullanabilirsiniz.

• Aşağı yuvarlama: Bir sayıyı aşağıya yuvarlamak.
• Round: Bir sayıyı yuvarlamak.
• Yuvarlama: Ondalık basamaklarımı ne kadar yuvarlayabileceğinizi seçebilirsiniz.

İşte adımlar:

• Sütunu seçin ve ➜ Dönüştür ➜ Yuvarlak öğesine sağ tıklayın .
  1. Aşağı yuvarlama: Bir sayıyı aşağıya yuvarlamak.
  2. Round: Bir sayıyı yuvarlamak.
  3. Yuvarlama: Ondalık basamaklarımı ne kadar yuvarlayabileceğinizi seçebilirsiniz.

Not: “#3 Yuvarlak” seçeneğini seçtiğinizde yuvarlanacak ondalık basamak sayısını girmeniz gerekir.

18. Hesaplamalar

Hesaplamalar yapmak için kullanabileceğiniz seçenekler vardır (bunlardan birçoğu). Tüm bu seçenekleri Dönüştür sekmesinde (Sayısal Sütun grubunda) bulabilirsiniz.

• Temel
• İstatistik
• Bilim adamı
• Trigonometri
• Yuvarlak
• Bilgi

Bu hesaplamalardan herhangi birini gerçekleştirmek için sütunu ve ardından seçeneği seçmelisiniz.

19. Gruplandırma ölçütü

Diyelim ki geniş bir veri kümeniz var ve bir özet tablo oluşturmak istiyorsunuz. İşte yapmanız gerekenler:

• Dönüştürme sekmesinde ‘Gruplandırma ölçütü’ düğmesine tıklayın, bir iletişim kutusu göreceksiniz.

• Şimdi bu iletişim kutusundan gruplamak istediğiniz sütunu seçin ve ardından bir ad ekleyin, işlemi ve değerlerin bulunduğu sütunu seçin.

• Sonunda Tamam’ı tıklayın.

Not: Çok düzeyli bir grup tablosu oluşturmak için kullanabileceğiniz “Gruplandırma Ölçütü” seçeneğinde gelişmiş seçenekler de vardır.

20. Negatif Değerleri Kaldır

Blog yazılarımdan birinde olumsuz işaretleri kaldırmak için yedi yöntem listeledim ve güç sorgusu da bunlardan biri. Bir sütuna sağ tıklayın ve Dönüştür seçeneğine gidin, ardından “Mutlak Değer”e tıklayın.

Bu, değerlerden tüm olumsuz işaretleri anında kaldırır.

E-tabloya veri nasıl yüklenir

Verilerinizi dönüştürdükten sonra elektronik tabloya yükleyebilir ve daha fazla analiz için kullanabilirsiniz. Ana sekmede “Kapat ve Yükle” adında bir düğme vardır, ona tıkladığınızda ek seçenekler sunan bir açılır liste görüntülenir:

• Kapat ve yükle
• Kapatın ve yükleyin

• Düğmeye tıkladığınızda aşağıdaki seçenekler görüntülenecektir:

• Bu verileri e-tablonuzda nasıl görüntülemek istediğinizi seçin.
  • Tablo
  • Pivot Tablo Raporu:
  • Tablo pivotu
  • Yalnızca bir bağlantı oluştur
  • Verileri nereye koymak istiyorsunuz?
    • Mevcut Çalışma Sayfası
    • Yeni çalışma sayfası.
  • Bu verileri veri modeline ekleyin.
• Tablo seçeneğini ve yeni çalışma sayfasını seçip veri modelini işaretlemeden bırakın ve Tamam’a tıklayın.

• Tamam’ı tıkladığınız anda verilerle birlikte yeni bir çalışma sayfası eklenir.

Öğrenilecek daha fazla örnek

Sorguyu otomatik olarak yenileme

Burada verdiğim örnekler arasında en önemlisi bu. Bir sorgu oluşturduğunuzda onu otomatik olarak yenileyebilirsiniz (bir zamanlayıcı ayarlayabilirsiniz).

Ve işte adımlar:

• Veri sekmesinde “Sorgular ve Bağlantılar”a tıklayın; pencerenin sağ tarafında Sorgular ve Bağlantı bölmesini göreceksiniz.

• Şimdi sorguya sağ tıklayın ve “Her yenile” seçeneğini işaretleyin ve dakikaları girin.

Power Query’de formül ve işlev nasıl kullanılır?

Tıpkı bir Excel elektronik tablosundaki işlevleri ve formülleri kullanabildiğiniz gibi, Gelişmiş Sorgu’nun da kullanabileceğiniz kendi işlevler listesi vardır. PowerQuery’deki işlev ve formüllerin temelleri, Excel’in elektronik tablo işlevleriyle aynıdır.

PQ’da bir işlev veya formül eklemek için yeni bir özel sütun eklemeniz gerekir.

Bir örnek verelim: Aşağıdaki verilerde (PQ editöründe zaten mevcut) adınız ve soyadınız var ( DOWNLOAD LINK ) .

İki adı birleştirmeniz ve tam ad için bir sütun oluşturmanız gerektiğini düşünün. Bu durumda iki sütunun adlarını birleştirmek için basit bir formül girebilirsiniz.

• Öncelikle Sütun Ekle sekmesine gidin ve “Özel Sütun”a tıklayın.

• Şimdi özel sütun iletişim kutusuna yeni sütunun adını “Tam Ad” veya yeni sütuna ne isim vermek istiyorsanız girin .

• • Özel sütun formülü, formülü girmeniz gereken yerdir. Öyleyse aşağıdaki formülü girin :

 [First Name]&" "&[Last Name] 

• “Özel Sütun Formülü”ne bir formül girdiğinizde PQ, girdiğiniz formülü kontrol eder ve “Sözdizimi hatası tespit edilmedi” mesajını görüntüler ve eğer bir hata varsa, hatanın türüne göre bir mesaj hatası görüntüler.
• Formülü girdikten ve hiçbir hata içermedikten sonra Tamam’a basmanız yeterlidir.
• Artık verilerin sonunda iki sütundan (ad ve soyadı) değerleri içeren yeni bir sütununuz var.

Power Query’de bir işlev nasıl kullanılır?

Benzer şekilde, özel bir sütun eklerken bir işlevi de kullanabilirsiniz ve Power Query’de kullanabileceğiniz çok sayıda işlev listesi vardır.

Kolay ve basit bir örnekle bir fonksiyonun nasıl kullanılacağını anlayalım. Ad ve soyadını birleştiren yeni bir sütun eklediğimiz yukarıdaki örneğe devam ediyorum.

Ancak şimdi bu sütunda bulunan tam ad metnini büyük harfe dönüştürmeniz gerekiyor. Kullanabileceğiniz fonksiyon “ Text.Upper ” dır. Adından da anlaşılacağı gibi metni büyük harfe dönüştürür.

• Öncelikle Sütun Ekle sekmesine gidin ve özel sütuna tıklayın.

• • Şimdi, özel sütun iletişim kutusunda, özel sütun formül kutusuna sütun adını ve aşağıdaki formülü girin:

 Text.Upper([Full Name]) 

• Tamam’ı tıklattığınızda, tüm adların büyük harflerle yazıldığı yeni bir sütun oluşturulur.
• Bir sonraki adım eski sütunu silmek ve yeni sütunu yeniden adlandırmaktır. Bu yüzden ilk sütuna sağ tıklayın ve sil’i seçin.

• Sonunda yeni sütunu “Tam Ad” olarak yeniden adlandırın.

Power query’te yeni sütun eklerken kullanabileceğiniz toplam 700 fonksiyon mevcut ve işte Microsoft’un bu fonksiyonlar için sağladığı tam liste , göz atın.

PQ’da bir sorgu nasıl değiştirilir?

Zaten çalışma kitabınızda bulunan sorguda değişiklik yapmak istiyorsanız, sorguyu düzenleyip daha sonra bu değişiklikleri yapabilirsiniz. Veri sekmesinde Sorgular ve Bağlantılar adında bir düğme vardır.

Bu düğmeye tıkladığınızda sağ tarafta geçerli çalışma kitabındaki tüm sorguların listelendiği bir bölme açılır.

Sorgu adına sağ tıklayıp düzenle’yi seçebilirsiniz ve onu düzenlemek için güçlü sorgu düzenleyicisine alabilirsiniz.

Bir sorguyu düzenlediğinizde, daha önce gerçekleştirdiğiniz tüm adımların, sizin de düzenleyebileceğiniz veya yeni adımlar gerçekleştirebileceğiniz “Uygulanan Adımlar” içerisinde listelendiğini görebilirsiniz.

Düzenlemelerinizi tamamladığınızda “Kapat ve Yükle” düğmesini tıklamanız yeterlidir.

Bağlantıları dışa ve içe aktarma

Bir sorgu için kullandığınız bir bağlantınız varsa ve şimdi bu bağlantıyı başka biriyle paylaşmak istiyorsanız bu bağlantıyı bir odc dosyası olarak dışarı aktarabilirsiniz.

Sorgu tablosunda “Bağlantıyı Dışa Aktar” adında bir buton bulunmaktadır ve bu tuşa tıkladığınızda o sorgunun bağlantısını sisteminize kaydetmenizi sağlar.

Başka biri tarafından paylaşılan bir bağlantıyı içe aktarmak istiyorsanız, Veri sekmesine gidip Al ve Dönüştür bölümünde Mevcut Bağlantılar’a tıklamanız yeterlidir.

Ardından, sizinle paylaşılan bağlantı dosyasını bulabileceğiniz ve çalışma kitabınıza aktarabileceğiniz “Daha fazlası için göz at” düğmesine tıklayın.

PowerQuery dili (M kodu)

Daha önce de belirttiğim gibi PowerQuery’de attığınız her adım için M Code adı verilen bir kod (arka uçta) üretir. Ana Sayfa sekmesinde kodu görüntülemek için kullanabileceğiniz ” Gelişmiş Düzenleyici” adında bir düğme bulunmaktadır.

Ve gelişmiş düzenleyiciye tıkladığınızda size kod düzenleyiciyi gösterecek ve bu kod şuna benzer:

M, büyük/küçük harfe duyarlı bir dildir ve diğer tüm diller gibi değişkenleri ve ifadeleri kullanır. Kodun temel yapısı aşağıdaki gibi olup kodun LET ifadesi ile başladığı yerdir.

Bu kodda iki değişkenimiz ve bunlara ayarlanmış değerler var. Sonunda değeri elde etmek için IN ifadesi kullanıldı. Artık Tamam’a tıkladığınızda sonuçta “Değişkenadı” değişkenine atanan değeri döndürecektir.

Power Query Dili hakkında daha fazla bilgi edinmek için bu kaynağa göz atın.

Sonunda

POWER QUERY’den kaçınmayı göze alamazsınız. Eğer böyle düşünüyorsanız, Excel işlevleri veya VBA kodları ile yaptığımız pek çok şey onu kullanarak otomatik hale getirilebilir ve eminim ki bu eğitim size onu daha fazla kullanmanız için ilham verecektir.