Power Query, birden çok çalışma kitabını tek bir çalışma kitabında birleştirmek istediğinizde çok yardımcı olabilir.
Örneğin, farklı bölgeler (Doğu, Batı, Kuzey ve Güney) için satış verileriniz olduğunu varsayalım. Power Query'yi kullanarak farklı çalışma kitaplarındaki bu verileri tek bir çalışma sayfasında birleştirebilirsiniz.
Bu çalışma kitaplarınız farklı konumlarda/klasörlerde varsa, tüm bunları tek bir klasöre taşımak (veya bir kopya oluşturup o çalışma kitabı kopyasını aynı klasöre koymak) iyi bir fikirdir.
Başlangıç olarak, bir klasörde dört çalışma kitabım var (aşağıda gösterildiği gibi).
Şimdi, bu öğreticide, Power Query kullanarak farklı çalışma kitaplarındaki verileri birleştirebileceğiniz üç senaryoyu ele alıyorum:
- Her çalışma kitabında bir Excel Tablosunda veriler bulunur ve tüm tablo adları aynıdır.
- Her çalışma kitabında aynı çalışma sayfası adına sahip veriler bulunur. Bu, tüm çalışma kitaplarında 'özet' veya 'veri' adında bir sayfa olduğunda ve bunların hepsini birleştirmek istediğinizde olabilir.
- Her çalışma kitabında birçok sayfa ve tablo vardır ve belirli tabloları/sayfaları birleştirmek istiyorsunuz. Bu yöntem, tutarlı bir ada sahip olmayan tablo/sayfaları birleştirmek istediğinizde de yardımcı olabilir.
Her durumda bu çalışma kitaplarındaki verilerin nasıl birleştirileceğini görelim.
Her çalışma kitabında, aynı yapıya sahip bir Excel Tablosunda veriler bulunur.
Aşağıdaki teknik, Excel Tablolarınız aynı şekilde yapılandırıldığında (aynı sütun adları) işe yarar.
Her tablodaki satır sayısı değişebilir.
Bazı Excel Tablolarında ek sütunlar varsa endişelenmeyin. Şablon olarak (veya Power Query'nin dediği gibi "anahtar" olarak) Tablolardan birini seçebilirsiniz; Power Query, diğer tüm Excel Tablolarını onunla birleştirmek için kullanır.
Diğer tablolarda ek sütunlar olması durumunda, bunlar yok sayılır ve yalnızca şablonda/anahtarda belirtilenler birleştirilir. Örneğin, seçtiğiniz şablon/anahtar tablosunda 5 sütun varsa ve başka bir çalışma kitabındaki tablolardan birinde 2 ek sütun varsa, bu ek sütunlar yoksayılır.
Şimdi bir klasörde birleştirmek istediğim dört çalışma kitabım var.
Aşağıda, çalışma kitaplarından birinde sahip olduğum tablonun bir görüntüsü var.
Bu çalışma kitaplarındaki verileri tek bir çalışma kitabında (tek bir tablo olarak) birleştirme adımları aşağıda verilmiştir.
- Veri sekmesine gidin.
- Al ve Dönüştür grubunda, Yeni Sorgu açılır menüsüne tıklayın.
- İmlecinizi "Dosyadan" üzerine getirin ve "Klasörden" üzerine tıklayın.
- Klasör iletişim kutusunda, dosyaların bulunduğu klasörün dosya yolunu girin veya Gözat'a tıklayın ve klasörü bulun.
- Tamam'ı tıklayın.
- Açılan iletişim kutusunda, birleştir düğmesine tıklayın.
- 'Birleştir ve Yükle' üzerine tıklayın.
- Açılan 'Dosyaları Birleştir' iletişim kutusunda, sol bölmedeki Tablo'yu seçin. Power Query'nin size ilk dosyadaki Tabloyu gösterdiğini unutmayın. Bu dosya, diğer dosyaları birleştirmek için şablon (veya anahtar) görevi görür. Power Query artık diğer çalışma kitaplarında 'Tablo 1'i arar ve bunu bununla birleştirir.
- Tamam'ı tıklayın.
Bu, nihai sonucu (birleşik veriler) aktif çalışma sayfanıza yükleyecektir.
Power Query'nin verilerle birlikte otomatik olarak çalışma kitabı adını birleştirilmiş verilerin ilk sütunu olarak eklediğini unutmayın. Bu, hangi verilerin hangi çalışma kitabından geldiğini takip etmeye yardımcı olur.
Verileri Excel'e yüklemeden önce Düzenlemek istiyorsanız, Adım 6'da 'Birleştir ve Düzenle'yi seçin. Bu, verileri düzenleyebileceğiniz Power Query düzenleyicisinde nihai sonucu açacaktır.
Bilmeniz gereken birkaç şey:
- Şablon olarak bir Excel Tablosu seçerseniz (7. Adımda), Power Query, diğer Tablolardaki verileri birleştirmek için bu Tablodaki sütun adlarını kullanır. Diğer Tablolarda ek sütunlar varsa, bunlar yok sayılır. Bu diğer Tabloların Şablon Tablonuzda bulunan bir sütunu olmaması durumunda, Power Query bunun için sadece 'boş' koyar.
- Power Query, sütunları eşlemek için sütun başlıklarını kullandığından, sütunların aynı sırada olması gerekmez.
- Anahtar olarak Tablo1'i seçtiğiniz için, Power Query tüm çalışma kitaplarında Tablo1'i arayacak ve tüm bunları birleştirecektir. Aynı ada sahip bir Excel Tablosu (bu örnekte Tablo1) bulamazsa, Power Query size bir hata verecektir.
Klasöre Yeni Dosya Ekleme
Şimdi bir dakikanızı ayıralım ve yukarıdaki adımlarla ne yaptığımızı anlayalım (ki bu sadece birkaç saniyemizi aldı).
Dört farklı çalışma kitabından gelen verileri, hiçbir çalışma kitabını açmadan birkaç saniye içinde tek bir tabloda birleştirdik.
Ama hepsi bu değil.
Power Query'nin gerçek GÜCÜ, artık klasöre daha fazla dosya eklediğinizde bu adımların hiçbirini tekrarlamanıza gerek kalmamasıdır.
Tek yapmanız gereken klasördeki yeni çalışma kitabını taşımak, sorguyu yenilemek ve o klasördeki tüm çalışma kitaplarındaki verileri otomatik olarak birleştirmek.
Örneğin, yukarıdaki örnekte, yeni bir çalışma kitabı eklersem - "Orta Batı.xlsx" klasöre ve sorguyu yenileyin, bana anında yeni birleştirilmiş veri kümesini verecek.
Bir sorguyu şu şekilde yenilersiniz:
- Çalışma sayfasına yüklediğiniz Excel Tablosuna sağ tıklayın ve Yenile'ye tıklayın.
- 'Çalışma Kitabı Sorgusu' bölmesindeki Sorguya sağ tıklayın ve Yenile'ye tıklayın.
- Veri sekmesine gidin ve Yenile'ye tıklayın.
Her çalışma kitabında aynı çalışma sayfası adına sahip veriler bulunur
Bir Excel Tablosundaki verilere sahip değilseniz, ancak tüm sayfa adları (verileri birleştirmek istediğiniz) aynıysa, bu bölümde gösterilen yöntemi kullanabilirsiniz.
Bir Excel Tablosu değil, yalnızca tablo verileri olduğunda dikkatli olmanız gereken birkaç şey vardır.
- Çalışma sayfası adları aynı olmalıdır. Bu, Power Query'nin çalışma kitaplarınızı gözden geçirmesine ve her çalışma kitabında aynı ada sahip çalışma sayfalarındaki verileri birleştirmesine yardımcı olur.
- Power Query büyük/küçük harfe duyarlıdır. Bu, 'veri' ve 'Veri' adlı bir çalışma sayfasının farklı kabul edildiği anlamına gelir. Benzer şekilde, "Mağaza" başlıklı bir sütun ve "mağaza" başlıklı bir sütun farklı kabul edilir.
- Aynı sütun başlıklarına sahip olmak önemli olsa da, aynı sıraya sahip olmak önemli değildir. "East.xlsx" dosyasındaki 2. sütun "West.xlsx" dosyasındaki 4. sütunsa Power Query, başlıkları eşleyerek onu doğru şekilde eşleştirir.
Şimdi, çalışma sayfası adının aynı olduğu farklı çalışma kitaplarındaki verileri hızlı bir şekilde nasıl birleştireceğimizi görelim.
Bu örnekte, dört dosya içeren bir klasörüm var.
Her çalışma kitabında, aşağıdaki biçimde verileri içeren 'Veri' adında bir çalışma sayfam var (bunun bir Excel Tablosu olmadığını unutmayın).
Birden çok çalışma kitabındaki verileri tek bir çalışma sayfasında birleştirme adımları şunlardır:
- Veri sekmesine gidin.
- Al ve Dönüştür grubunda, Yeni Sorgu açılır menüsüne tıklayın.
- İmlecinizi "Dosyadan" üzerine getirin ve "Klasörden" üzerine tıklayın.
- Klasör iletişim kutusunda, dosyaların bulunduğu klasörün dosya yolunu girin veya Gözat'a tıklayın ve klasörü bulun.
- Tamam'ı tıklayın.
- Açılan iletişim kutusunda, birleştir düğmesine tıklayın.
- 'Birleştir ve Yükle' üzerine tıklayın.
- Açılan "Dosyaları Birleştir" iletişim kutusunda, sol bölmede "Veri"yi seçin. Power Query'nin size ilk dosyadaki çalışma sayfası adını gösterdiğini unutmayın. Bu dosya, diğer dosyaları birleştirmek için anahtar/şablon görevi görür. Power Query, her çalışma kitabını gözden geçirecek, "Veri" adlı sayfayı bulacak ve tüm bunları birleştirecektir.
- Tamam'ı tıklayın. Şimdi Power Query her çalışma kitabını gözden geçirecek, içinde "Veri" adlı çalışma sayfasını arayacak ve ardından tüm bu veri kümelerini birleştirecektir.
Bu, nihai sonucu (birleşik veriler) aktif çalışma sayfanıza yükleyecektir.
Verileri Excel'e yüklemeden önce Düzenlemek istiyorsanız, Adım 6'da 'Birleştir ve Düzenle'yi seçin. Bu, verileri düzenleyebileceğiniz Power Query düzenleyicisinde nihai sonucu açacaktır.
Her Çalışma Kitabı, Farklı Tablo adlarına veya Sayfa Adlarına sahip verilere sahiptir.
Bazen yapılandırılmış ve tutarlı veriler elde edemeyebilirsiniz (aynı ada sahip tablolar veya aynı ada sahip çalışma sayfası gibi).
Örneğin, verileri bu veri kümelerini oluşturan, ancak çalışma sayfalarını Doğu Verileri, Batı Verileri, Kuzey Verileri ve Güney Verileri olarak adlandıran birinden aldığınızı varsayalım.
Veya kişi Excel tabloları oluşturmuş olabilir, ancak farklı adlarla.
Bu gibi durumlarda Power Query'yi kullanmaya devam edebilirsiniz, ancak bunu birkaç ek adımla yapmanız gerekir.
- Veri sekmesine gidin.
- Al ve Dönüştür grubunda, Yeni Sorgu açılır menüsüne tıklayın.
- İmlecinizi "Dosyadan" üzerine getirin ve "Klasörden" üzerine tıklayın.
- Klasör iletişim kutusunda, dosyaların bulunduğu klasörün dosya yolunu girin veya Gözat'a tıklayın ve klasörü bulun.
- Tamam'ı tıklayın.
- Açılan iletişim kutusunda Düzenle düğmesine tıklayın. Bu, klasördeki tüm dosyaların ayrıntılarını göreceğiniz Power Query düzenleyicisini açacaktır.
- Kontrol tuşunu basılı tutun ve "İçerik" ve "Ad" sütunlarını seçin, sağ tıklayın ve "Diğer Sütunları Kaldır"ı seçin. Bu, seçilen sütunlar dışındaki diğer tüm sütunları kaldıracaktır.
- Sorgu Düzenleyici şeridinde, "Sütun ekle"yi ve ardından "Özel Sütun"u tıklayın.
- Özel Sütun Ekle iletişim kutusunda, yeni sütunu 'Veri İçe Aktarma' olarak adlandırın ve aşağıdaki formülü kullanın. =Excel.Çalışma Kitabı([İÇERİK]). Bu formülün büyük/küçük harf duyarlı olduğunu ve tam olarak burada gösterdiğim şekilde girmeniz gerektiğini unutmayın.
- Şimdi içinde Tablo yazan yeni bir sütun göreceksiniz. Şimdi burada ne olduğunu açıklamama izin verin. Power Query'ye çalışma kitaplarının adlarını sağladınız ve Power Query, çalışma sayfaları, tablolar ve adlandırılmış aralıklar gibi nesneleri her çalışma kitabından (şu anda Tablo hücresinde bulunan) getirdi. Tablo metninin yanındaki beyaz boşluğa tıklayabilir ve bilgileri altta görebilirsiniz. Bu durumda, her çalışma kitabında yalnızca bir tablomuz ve bir çalışma sayfamız olduğundan, yalnızca iki satır görebilirsiniz.
- "Veri İçe Aktarma" sütununun üstündeki çift ok simgesini tıklayın.
- Açılan sütun veri kutusunda, 'Önek olarak orijinal sütunu kullan' seçeneğinin işaretini kaldırın ve ardından Tamam'ı tıklayın.
- Şimdi, tablodaki her nesne için bir satır gördüğünüz genişletilmiş bir tablo göreceksiniz. Bu durumda, her çalışma kitabı için sayfa nesnesi ve tablo nesnesi ayrı ayrı listelenir.
- Tür sütununda, listeyi yalnızca Tabloyu gösterecek şekilde filtreleyin.
- Kontrol tuşunu basılı tutun ve Ad ve Veri sütununu seçin. Şimdi sağ tıklayın ve diğer tüm sütunları kaldırın.
- Veri sütununda, Veri Başlığının sağ üst köşesindeki çift ok simgesine tıklayın.
- Açılan sütun veri kutusunda Tamam'ı tıklayın. Bu, tüm tablolardaki verileri birleştirecek ve Power Query'de gösterilecektir.
- Artık ihtiyacınız olan herhangi bir dönüşümü yapabilir ve ardından Giriş sekmesine gidip Kapat ve Yükle'ye tıklayabilirsiniz.
Şimdi burada ne yaptığımızı hızlıca açıklamaya çalışayım. Sayfa adlarında veya tablo adlarında tutarlılık olmadığından, Power Query'deki çalışma kitaplarının tüm nesnelerini getirmek için =Excel.Çalışma Kitabı formülünü kullandık. Bu nesneler sayfaları, tabloları ve adlandırılmış aralıkları içerebilir. Tüm dosyalardan tüm nesnelere sahip olduğumuzda, bunları yalnızca Excel Tablolarını dikkate alacak şekilde filtreledik. Daha sonra tablolardaki verileri genişlettik ve hepsini birleştirdik.
Bu örnekte, verileri yalnızca Excel Tablolarını kullanacak şekilde filtreledik (Adım 13'te). Tabloları değil, sayfaları birleştirmek istiyorsanız, sayfaları filtreleyebilirsiniz.
Not - bu teknik, sütun adlarında bir uyumsuzluk olsa bile size birleştirilmiş verileri verecektir. Örneğin, East.xlsx'te yanlış yazılmış bir sütununuz varsa, elinizde 5 sütun kalır. Power Query, verileri bulursa sütunlara doldurur ve bir sütun bulamazsa değeri 'boş' olarak bildirir.
Benzer şekilde, tablo çalışma sayfalarından herhangi birinde ek sütunlarınız varsa, bunlar nihai sonuca dahil edilecektir.
Şimdi, verileri birleştirmeniz gereken daha fazla çalışma kitabı alırsanız, bunları kopyalayıp klasöre yapıştırmanız ve Power Query'yi yenilemeniz yeterlidir.
