Excelde Power Query Nasıl Kullanılır: Eksiksiz Kılavuz

Power Query, verileri ihtiyaçlarınıza göre içe aktarmanıza, temizlemenize ve dönüştürmenize olanak tanıyan, Excel'de yer alan güçlü bir iş zekası aracıdır. Verileri temizlemek için saatler harcadıysanız (bizde var) veya çeşitli kaynaklardan verileri birleştirmek için mücadele ettiyseniz (bizde de var), bu özellik hayatınızı kolaylaştıracak.

Excel'de Power Query'yi nasıl kullanacağınızı öğrenmenin en iyi yolu, verileri içe aktarmak ve ortaya çıkan tabloları dönüştürmek ve verileri birleştirmek için Power Query Düzenleyicisi'ni kullanmak gibi bazı yaygın görevleri gerçekleştirmektir.

Bu makale , adım adım kılavuzlarla net Power Query örnekleri göstererek başlamanıza yardımcı olur . Power Query'de uzmanlaşarak işinizi bir sonraki seviyeye yükseltebilirsiniz.

Hazır mısın? Bunu yapalım.

İçindekiler

• Power Query'ye Erişip Erişemeyeceğinizi Nasıl Anlarsınız?
• Power Query ile Verileri İçe Aktarma
• Power Query Düzenleyicisi Nasıl Kullanılır?
  • Veri Önizleme
  • Özellikler
• Birden Çok Veri Kaynağıyla Nasıl Çalışılır?
• Power Query ile Veri Ekleme
• Power Query ile Verileri Birleştirme
• Power Query ile Verileri Dönüştürmenin 5 Yolu
  • 1. Verileri Genişletme ve Yoğunlaştırma
  • 2. Verileri Sıralama
  • 3. Verileri Biçimlendirme
  • 4. Sütun Ekleme ve Kaldırma
  • 5. Veri Türlerini Değiştirme
• Formüller ve M Dili ile Çalışma
  • Formül çubuğu
  • M Kodu Yazma ve Değiştirme
• M Dili Sözdizimi nedir?
• Veri Bağlantıları Nasıl Yenilenir
• Power Query'yi Veri Modeli İle Tümleştirme
• Power Query ve VBA ile Otomatikleştirme
• Power Query Gelişmiş Özellikleri
• İşbirliği ve Paylaşım
• Bunu Bitirelim

Power Query'ye Erişip Erişemeyeceğinizi Nasıl Anlarsınız?

Power Query'yi kullanmaya başlamadan önce, bu özelliğe sahip bir Excel sürümüne sahip olduğunuzdan emin olmanız gerekir.

Sürüme göre Power Query'ye kısa bir genel bakış:

• Excel 2010 ve Excel 2013: indirilmesi ve kurulması gereken bir eklenti olarak mevcuttur.

• Excel 2016: "Al ve Dönüştür" olarak Excel'e entegre edilmiştir.

• Excel 2019: geliştirilmiş performans ve işlevsellik.

• Excel 2021 ve Microsoft 365 için Excel: bulut veri kaynaklarıyla tümleştirme.

• Mac için Excel: yalnızca Microsoft 365 sürüm 16.69 ve sonrasında kullanılabilir.

Aracın Excel çalışma kitabınıza (2016 veya sonrası) entegre edildiğini doğrulamak için Veri sekmesine gidin ve bu resimde "Verileri Al ve Dönüştür" bölümünü arayın:

Windows'ta Excel 2010 veya 2013 kullanıyorsanız, Power Query eklentisini indirebilir , yükleyebilir ve etkinleştirebilirsiniz.

Power Query ile Verileri İçe Aktarma

Power Query aracı, çok çeşitli veri kaynaklarını içe aktarmanıza ve bunlara bağlanmanıza olanak tanır:

• CSV, XML ve JSON biçimleri dahil olmak üzere dosyalar

• Web URL'leri veya API uç noktaları

• Access, SQL Server, Oracle ve en modern veritabanları dahil olmak üzere veritabanları

• Azure hizmetleri, SharePoint dosyaları ve OData akışları

Başlangıç ​​için en kolay veri kaynağı, birkaç veri satırı içeren basit bir CSV dosyasıdır. Bir metin dosyası oluşturun ve bu dört adı ve soyadını virgülle ayırarak ekleyin:

• Joe, Bloglar

• Anne, Ryan

• James, Stuart

• Meryem, Kahverengi

Dosyayı .CSV uzantısıyla kaydettiğinizden emin olun. Dosyanız şöyle görünecek:

Verileri Power Query ile yüklemek için şu adımları izleyin:

1. Veri sekmesine gidin.

2. Verileri Al ve Dönüştür sekmesindeki "Dosyaları Al" düğmesini tıklayın.

3. Örnek dosyanızı seçin.

Aracın verileri önizleme bölmesine çekmesi için birkaç saniye beklemeniz gerekebilir. Power Query, küçük bir içe aktarma işlemi için bile oldukça fazla makine kaynağı kullanır, bu nedenle lütfen sabırlı olun.

İpucu: performansı hızlandırmak için diğer uygulamaları kapatın.

Power Query, verilerinizin önizlemesini içeren "Metin/CSV İçe Aktarma" penceresini görüntüler.

Veriler doğru görünüyorsa, verileri yüklemeye veya dönüştürmeye devam edebilirsiniz:

• Yükle düğmesi, verileri doğrudan Excel'e yükleyecektir.

• Verileri Dönüştür düğmesi, verileri yüklemeden önce değiştirmenize olanak tanır.

Örnek dosyanın bir başlığı olmadığını fark ettiniz mi? Doğrudan Excel'e yüklenirse, başlıklar "Sütun 1" ve "Sütun 2" olacaktır.

Bunun yerine, verileri Power Query düzenleyicisine çekip bazı değişiklikler yapabilirsiniz. Bunu yapmak için "Verileri Dönüştür" düğmesini tıklayın.

Şimdi Power Query Düzenleyicisi açılıyor ve bu da bizi bir sonraki bölüme getiriyor.

Power Query Düzenleyicisi Nasıl Kullanılır?

Bu bölümde, Power Query Düzenleyicisi ile çalışmanın bazı temel yönlerini keşfedeceğiz.

Veri Önizleme

Veri Önizleme alanında, içe aktarılan verilerinizi görüntüleyebilir ve bunlarla etkileşim kurabilirsiniz. Satırları filtreleyebilir, sütun veri türlerini değiştirebilir ve verileri ihtiyaçlarınıza göre yeniden şekillendirebilirsiniz.

İşiniz bittiğinde, Excel dönüştürülen verileri bir çalışma sayfasına yükleyecektir.

Örnek dosyayı kullanarak sütun adlarını doğrudan bu bölmede düzenleyebilirsiniz.

Böyle yaparak:

1. Sütun başlığını sağ tıklayın.

2. Açılır menüde "Yeniden Adlandır"ı tıklayın.

3. Tercih edilen adları başlık kutularına yazın.

Siz değişiklik yaptıkça, önizleme bölmesinin sağındaki "Uygulanan Adımlar" bölümü her dönüşümü yansıtacak şekilde güncellenerek veri işleme adımlarınızı gerektiği gibi izlemenize ve değiştirmenize olanak tanır.

Özellikler

Sorgularınız için özellikler ayarlamak, çalışmanızı organize etmek ve izlemek için çok önemlidir.

Sorgu Ayarları bölmesi, Power Query penceresinin sağ tarafındadır.

1. "Tüm Özellikler" bağlantısını tıklayın.

2. Açıklayıcı olacak şekilde Sorgunun adını düzenleyin.

3. Daha fazla bilgi sağlamak için bir açıklama ekleyin.

Geçen haftaki sorguların ne için ayarlandığını unutmak kolaydır. Bu durumda sorgu adı değiştirilmiş ve sorgunun amacını veren bir açıklama eklenmiştir.

Mevcut seçenekler hakkında daha fazla bilgi edinmek istiyorsanız, Power Query Düzenleyicisi'ne yönelik bu genel bakışa göz atın .

Birden Çok Veri Kaynağıyla Nasıl Çalışılır?

Power Query Düzenleyicisi, birden çok kaynaktan gelen verileri birleştirmek için iki temel yöntem sunar: ekleme ve birleştirme.

Ekleme, tabloları dikey olarak birleştirir ve üst üste istifler. Birleştirme, tabloları ortak sütunlara göre birleştirmenizi sağlar.

Bunun nasıl çalıştığını görmek için birincisiyle aynı yapıya sahip ikinci bir küçük CSV dosyası oluşturun. Bu kez, dosyaya iki ad (birinci ve son) girin:

• Joe, Bloglar

• Laura, Can

Bu adlardan birinin önceki dosyadan tekrar edildiğine dikkat edin.

Şimdi verileri Microsoft Excel çalışma kitabınıza çekmek için şu adımları izleyin.

1. Power Query Düzenleyicisi penceresini kapatın ve değişikliklerinizi kaydedin.

2. Yeni bir çalışma sayfası ekleyin.

3. İkinci dosyayı içe aktarmak için önceki bölümdeki adımları izleyin.

4. Dönüştür düğmesine tıklayın.

5. Sütun başlıklarını “Ad” ve “Soyadı” olarak yeniden adlandırın.

Excel çalışma sayfasındaki sağ bölme, geçerli Sorguları ve Bağlantıları gösterir. Şimdi bölmede iki sorgu görmelisiniz.

Power Query ile Veri Ekleme

Bu bölümde, ikinci örnek CSV dosyasını birinciye ekleyeceksiniz.

Veri eklemek için şu adımları izleyin:

1. Sorgular ve Bağlantılar bölmesindeki ilk tabloya (Müşteri Adları) sağ tıklayın.

2. Tabloyu Power Query Düzenleyicisi'nde açmak için "Düzenle"yi seçin.

3. Birleştir bölümünde Sorguları Ekle'ye tıklayın.

4. Eklenecek tabloların açılır listesinden ikinci tabloyu seçin.

Sonuç, ilk tabloda fazladan iki satır olacaktır.

Bir satırın tekrarlandığına dikkat edin - ekleme işlemi bu şekilde çalışır. Verilerin zaten var olup olmadığına bakılmaksızın, verileri bir tablodan diğerine ekler.

Power Query Düzenleyicisi'ndeki yinelenen verileri şu adımlarla kaldırabilirsiniz :

1. "Ad" sütun başlığına tıklayın.

2. “Ctrl” tuşunu (veya Mac'te “Cmd” tuşunu) basılı tutun.

3. Her iki sütun başlığına da tıklayın.

4. Power Query Düzenleyicisi şeridinin Giriş sekmesindeki "Satırları Kaldır"a tıklayın.

5. Açılır menüden "Kopyaları Kaldır"ı seçin.

Power Query ile Verileri Birleştirme

Verileri birleştirirken, sorgular arasında eşleşen sütunları seçmelisiniz.

Birinin müşteri telefon numaralarını ve diğerinin adresleri içerdiği virgülle ayrılmış iki dosyanız olduğunu varsayalım. İki dosya şöyle görünür:

Her dosyayı kendi güçlü sorgusuna yüklemek için önceki bölümlerdeki talimatları izleyin. Ardından tabloları birleştirme alıştırması yapabilirsiniz.

Örnek verilerimiz söz konusu olduğunda, eşleşen sütunlar olarak her iki sütun da (Ad ve Soyadı) seçilmelidir. Birden çok sütun seçmek için Ctrl tuşunu basılı tutun .

Bu adımları takip et:

1. Sorgular ve Bağlantılar bölmesindeki ilk tabloya sağ tıklayın.

2. Tabloyu Power Query Düzenleyicisi'nde açmak için "Düzenle"yi seçin.

3. “Birleştir” grubundan “Sorguları Birleştir”i seçin.

4. Tabloların açılır listesinden ikinci tabloyu seçin.

5. İlk tablodaki ilk sütuna tıklayın.

6. Ctrl tuşunu basılı tutun ve ilk tablodaki ikinci sütuna tıklayın.

7. Ctrl tuşunu basılı tutun ve ikinci tablodaki her iki sütuna tıklayın.

8. Varsayılan birleştirme işlecini "Sol Dış" olarak tutun.

9. Değişiklikleri uygulamak için "Tamam" ı tıklayın.

"Sol Dış" birleştirme operatörü, yinelenen verilerin ortadan kaldırılmasını sağlar.

Power Query Düzenleyici'de, ikinci sorgudan birleştirilmiş verileri içeren "Tablo" veya "İç İçe Tablo" adlı yeni bir sütun göreceksiniz.

1. Bu sütunun başlığındaki genişlet simgesine (iki ok) tıklayın.

2. "Orijinal sütunu önek olarak kullan" kutusunun işaretini kaldırın (bu, uzun sütun adlarını önler).

3. "Tamam"a tıklayın.

4. "Kapat ve Yükle"ye tıklayın.

Birleştirilmiş tablonun, telefon ve adres ayrıntılarının birleştirilmiş olduğu dört sütunu vardı. Hala yapacak biraz toparlamanız olabilir.

Örneğin aşağıdaki resimdeki tablonun adını (Merge1) ve adres sütununu düzenlemek isteyebilirsiniz.

Power Query ile Verileri Dönüştürmenin 5 Yolu

Excel dosyanızdaki verileri dönüştürmek, verilerinizi etkili bir şekilde temizlemenize, düzenlemenize ve değiştirmenize olanak tanıyan Power Query ile kolaylaştırılmıştır.

Verileri dönüştürmenin en yaygın yollarından bazıları şunlardır:

• Verileri genişletme ve yoğunlaştırma

• Verileri sıralama

• Veri formatlama

• Sütun ekleme ve kaldırma

• Veri türlerini değiştirme

1. Verileri Genişletme ve Yoğunlaştırma

Veri kümelerinizi daha yönetilebilir hale getirmek için verileri genişletmek ve yoğunlaştırmak önemlidir. Power Query, daha düzenli bir görünüm için tabloları genişletmenize veya daraltmanıza olanak tanır.

Önceki bölüm, ilgili tablolardan verileri dahil ederek nasıl genişleteceğinizi gösterir.

Toplam, sayım veya yüzde gibi seçtiğiniz bir toplamaya dayalı olarak yalnızca özetlenmiş verileri içerecek şekilde de kısaltabilirsiniz.

2. Verileri Sıralama

Verileri sıralamak için, artan veya azalan değerlere göre sırayı hızla değiştirebilirsiniz.

Bir sütun adının yanındaki aşağı oka tıklayın ve "Artan Sıralama" veya "Azalan Sıralama" seçeneğini seçin.

3. Verileri Biçimlendirme

Power Query Düzenleyicisi'ndeki Dönüştür sekmesi birçok seçenek sunar.

Örneğin, Biçim açılır menüsü, bir sütundaki veya tüm tablodaki tüm verileri büyük veya küçük harf olarak değiştirmenize olanak tanır.

4. Sütun Ekleme ve Kaldırma

Power Query, sütun ekleme, silme ve değiştirme sürecini basitleştirir.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

Örneğin, daha kolay hesaplama ve karşılaştırma için ondalık noktaları yüzdelere dönüştürmeniz veya metin tabanlı yüzdeleri sayısal yüzdelere dönüştürmeniz gerekebilir.

Formüller ve M Dili ile Çalışma

Excel'de Power Query kullanırken, gelişmiş formüller oluşturmanın, verileri işlemenin ve güçlü sorgular üretmenin kalbi olan M Dili ile karşılaşacaksınız. Bu bölümde, şunları öğreneceksiniz:

• formül çubuğu

• M kodu

• M Dili sözdiziminin temel öğeleri

Formül çubuğu

Excel'in Power Query düzenleyicisindeki Formül Çubuğu, formüllerinizi ve M kodunuzu gireceğiniz alandır.

Kullanmak için Sorgular ve Bağlantılar bölmesinden bir sorgu seçerek Power Query düzenleyicisine gidin. Formül Çubuğu, veri ön izleme bölmesinin üzerinde bulunur.

M Kodu Yazma ve Değiştirme

Excel'de çok yönlü ve güçlü sorgular oluşturmak için M kodu olarak bilinen Power Query dilini yazmanız ve değiştirmeniz gerekir. İşte nasıl başlayacağınızla ilgili bazı ipuçları:

• Sorgunuzun mantığını izlemeyi ve değiştirmeyi kolaylaştıran adım adım kod yazmak için Uygulanan Adımlar bölmesini kullanın.

• Dinamik ve otomatikleştirilmiş raporlar oluşturmak için M kodunu bir Excel tablosu, pivot tablolar ve Azure ve metin dosyaları gibi harici veri kaynaklarıyla birleştirin.

• Veri yükleme tercihinizi hassaslaştırmak ve performansı artırmak için Sorgu Seçeneklerini ayarlayın.

M koduna alıştıkça, Excel'deki veri analizinizi daha verimli ve güçlü hale getirerek birçok işlemi otomatikleştirebileceğinizi ve optimize edebileceğinizi göreceksiniz.

Bir sorgunun arkasındaki kodu görüntülemek için şu adımları izleyin:

1. Sorguyu Power Query Düzenleyicisi'nde açın.

2. Ana Sayfa sekmesindeki “Gelişmiş Düzenleyici”ye tıklayın.

Bu, örnek birleştirilmiş tablomuzun arkasındaki koddur (sizinki farklı görünebilir):

M Dili Sözdizimi nedir?

Etkili Power Query formülleri oluşturmak için M Dili sözdizimini anlamak çok önemlidir. Aşağıdaki noktaları aklınızda bulundurun:

• M Dili büyük/küçük harfe duyarlıdır, bu nedenle büyük harf kullanımına dikkat edin.

• Sütunlara, kayıtlara ve listelere başvurmak için köşeli parantez [] kullanın.

• Sorgunuzdaki önceki bir adıma başvurmak için değişken adını kullanın (örneğin, #”Adım Adı”).

• "in" ifadesiyle bir sorgu formül adımı çıktısı alın.

Power Query dil akışıyla deneyim kazandıkça , verilerinizi dönüştürmenin ve analiz etmenin yeni yollarını keşfederek Excel çalışma kitaplarınızı daha da değerli bir varlık haline getireceksiniz.

Aşağıdaki videoya göz atın, ovalama fırçasını çıkarmanın ve dağınık verilerinizi temizlemenin zamanı geldi.

Veri Bağlantıları Nasıl Yenilenir

Power Query'nin temel özelliklerinden biri, Power Query bağlantınızı kolayca yenileme yeteneğidir.

Veri kaynaklarınız güncellendiğinde ve çalışma kitabınızdaki en son bilgilere ihtiyacınız olduğunda, Sorgular ve Bağlantılar bölmesinde veri bağlantılarını yenileyebilirsiniz.

Çalışma sayfanızın sağındaki bölmeyi görmüyorsanız, Veri tablosuna gidip “Sorgular ve Bağlantılar” seçeneğine tıklayarak açabilirsiniz.

Sorgular ve Bağlantılar bölmesi açıkken tüm sorguları listeler. Bu adımları takip et:

1. Yenilemek istediğiniz sorguya sağ tıklayın.

2. Bağlam menüsünden Yenile'yi seçin.

Bu, çalışma kitabınızı bağlı veri kaynaklarınızdaki en son verilerle güncelleyecektir.

Power Query'yi Veri Modeli İle Tümleştirme

Excel'in veri modeli, veri ilişkilerine dayalı olarak pivot tablolar gibi iş zekası araçları oluşturmanıza olanak tanıyan güçlü bir özelliktir.

Veri modeli temel olarak içe aktarılan verileri ve tablolar arasındaki ilişkileri tutan gizli bir veritabanıdır.

Bu, Excel'in geçerli sürümlerinde yerleşik olarak bulunan Power Pivot özelliğinin bir parçasıdır. Önceki sürümlerde erişmek için Power Pivot'u bir eklenti olarak yüklemeniz gerekebilir).

Power Query veri bağlantılarınızı şu adımlarla veri modeliyle tümleştirebilirsiniz:

1. Önceki bölümlerde açıklandığı gibi Power Query kullanarak verilerinizi yükleyin.

2. Doğrudan çalışma kitabına yüklemek yerine, Yükle menüsünden Yükle'yi seçin.

3. Verileri İçe Aktar iletişim kutusunda Bu verileri Veri Modeli'ne ekle onay kutusunu seçin.

Power Query ve VBA ile Otomatikleştirme

VBA (Visual Basic for Applications) entegrasyonu ile Power Query'de özel veri bağlantıları oluşturabilir ve veri dönüştürme adımlarını otomatikleştirebilirsiniz.

Microsoft Access, SharePoint ve Analysis Services gibi platformlardan alınan verileri birleştirerek gelişmiş veri modelleri oluşturmak için mevcut VBA becerilerinizden yararlanabilirsiniz.

Power Query'nin VBA için yerel desteği olmasa da, Power Query sorgularını dolaylı olarak açmak, yenilemek ve değiştirmek için VBA kodunu kullanabilirsiniz.

Bu, güçlü, otomatikleştirilmiş veri iş akışları oluşturmanıza ve veri analizi sürecinizi kolaylaştırmanıza olanak tanır.

Burada, geçerli çalışma kitabındaki bir güç sorgusunu yenileyen bazı örnek kodlar verilmiştir.

:

Çalışma Kitabı Olarak wb'yi Dim, Nesne Olarak pq

Set wb = ThisWorkbook

Set pq = wb.Queries(“Müşteri Adları”)

 pq.Yenile

İşte bir Excel VBA makrosu içindeki kod:

Power Query Gelişmiş Özellikleri

Excel'in Power Query'si, veri analizi sürecinizi basitleştirmek ve geliştirmek için çok sayıda gelişmiş özellik sunar. Bu özellikleri etkili bir şekilde nasıl kullanacağınızı öğrenerek, veri analizi becerilerinizi yepyeni bir düzeye taşıyabilirsiniz.

Yerleşik veri analizi araçları şunları içerir:

• Veri dönüşümlerinize karmaşık mantık uygulamak için koşullu sütunlar.

• Verilerinizi anlamlı bir şekilde özetlemek için gruplama ve toplama işlevleri.

• Daha kolay analiz için verilerinizi yeniden şekillendirmeye yönelik Pivot ve Unpivot işlemleri.

Bu özelliklerin birçoğunu Power Query Düzenleyicisi'ndeki Dönüştür sekmesinde bulabilirsiniz.

Ayrıca, daha gelişmiş veri analizi ve görselleştirme seçenekleri için Power Query'yi LuckyTemplates gibi İş Zekası araçlarına bağlayabilirsiniz .

İşbirliği ve Paylaşım

Etkili işbirliği için veri bağlantılarınızı, sorgularınızı ve dönüşümlerinizi ekip üyelerinizle paylaşmak çok önemlidir.

Bir çalışma kitabını bir ağ sürücüsüne, SharePoint'e veya başka bir paylaşılan depolama konumuna kaydederek sorgularla paylaşabilirsiniz .

M kodunu aşağıdaki adımlarla tek bir sorgunun arkasına da aktarabilirsiniz:

1. Power Query Düzenleyicisi'nde sorguyu düzenleyin.

2. Ana Sayfa sekmesindeki “Gelişmiş Düzenleyici”ye tıklayın.

3. Advanced Editor penceresinde tüm M kodunu kopyalayın.

4. Bir metin düzenleyici (örneğin, Not Defteri) açın ve kopyalanan M kodunu metin düzenleyiciye yapıştırın.

5. Metin dosyasını kaydedin ve iş arkadaşınıza gönderin.

Bunu Bitirelim

Bir dizi özelliği ve kullanıcı dostu arayüzüyle Power Query, dış verilerle verimli bir şekilde çalışmak isteyen Excel kullanıcıları için önemli bir araçtır.

Bu değerli özellik, veri tablolarınızda rapor oluşturma, veri temizleme ve karmaşık analizler gerçekleştirme sürecini kolaylaştırmaya yardımcı olur. kullanma

Power Query, sonuç olarak size zaman kazandırır ve Excel dosyalarıyla çalışırken verimliliği artırır.