Birden çok veri kaynaklarını birleştirmeyi öğrenin (Power Query)

Bu öğreticide, Power Query'nin Sorgu Düzenleyicisi'ni kullanarak, ürün bilgileri içeren yerel bir Excel dosyasından ve ürün sipariş bilgileri içeren bir OData akışından verileri içeri aktarabilirsiniz. Dönüştürme ve toplama adımlarını gerçekleştirin ve "Ürün ve Yıl başına Toplam Satışlar" raporu üretmek için her iki kaynakta yer alan verileri bir araya gelirsiniz.   

Bu öğreticiyi gerçekleştirmek için Ürünler çalışma kitabı gerekir.Farklı Kaydet iletişim kutusunda, dosyayı Ürünler ve Siparişler.xlsx olarak adlandırın.

Bu görevde, Ürünler ve Orders.xlsx dosyalarındaki (yukarıda indirilmiş ve yeniden adlandırılmış) ürünleri bir Excel çalışma kitabına aktarıyor, satırları sütun başlıklarına yükseltıyor, bazı sütunları kaldırıyor ve sorguyu çalışma sayfasına yükleyebilirsiniz.

Adım 1: Excel çalışma kitabına bağlanma

  1. Bir Excel çalışma kitabı oluşturun.

  2. Çalışma Kitabından> Veri Al >'i >seçin.

  3. Veri İçeri Aktar iletişim kutusunda, indirdiğiniz Products.xlsx bulun ve aç'ı seçin.

  4. Gezgin bölmesinde Ürünler tablosuna çift tıklayın. Power Query Düzenleyicisi görüntülenir.

2. Adım: Sorgu Adımlarını inceleme

Varsayılan olarak, Power Query size kolaylık sağlamak için çeşitli adımları otomatik olarak ekler. Daha fazla bilgi edinmek için Sorgu Ayarları bölmesindekiUygulanan Adımlar'ın altındaki her adımı inceleyebilirsiniz.

  1. Kaynak adımına sağ tıklayın ve Ayarları Düzenle'yi seçin. Bu adım, çalışma kitabını içe aktarnızda oluşturulmuş.

  2. Gezinti adımına sağ tıklayın ve Ayarları Düzenle'yi seçin. Bu adım, Gezinti iletişim kutusundan tabloyu seçtiğiniz zaman oluşturulmuş.

  3. Değiştirilen Tür adımına sağ tıklayın ve Ayarları Düzenle'yi seçin. Bu adım, her sütunun veri türlerini oluşturulan Power Query tarafından oluşturulmuş. Formülün tamamında görmek için formül çubuğunun sağında yer alan aşağı oku seçin.

Adım 3: Yalnızca ilgili sütunları görüntülemek için diğer sütunları kaldırma

Bu adımda ÜrünKimliği, ÜrünAdı, KategoriKimliği ve ÜrünSayısı dışındaki tüm sütunları kaldıralım.

  1. Veri Önizleme'deÜrünKimliği, ÜrünAdı , KategoriKimliğive ÜrünKimliği sütunlarını seçin (Ctrl+Tıklama veya Shift+Tıklama kullanın).

  2. Sütunları Kaldır'>Diğer Sütunları Kaldır'ı seçin.

    Diğer sütunları gizleme

4. Adım: Ürünler sorgusunu yükleme

Bu adımda, Ürünler sorgusunu Bir Excel çalışma sayfasına yükleyebilirsiniz.

  • Giriş ve >Kapat'& seçin. Sorgu yeni bir Excel çalışma sayfasında görüntülenir.

Özet: Görev 1'de oluşturulan Power Query adımları

Power Query'de sorgu etkinlikleri gerçekleştir sonuç olarak, sorgu adımları oluşturulur ve Sorgu Ayarları bölmesindeki Uygulanan Adımlar listesinde listelenir. Her sorgu adımının, "M" dili olarak da bilinen, buna karşılık gelen bir Power Query formülü vardır. Power Query formülleri hakkında daha fazla bilgi için bkz. Excel'de Power Query formülleri oluşturma.

Görev

Sorgu adımı

Formül

Excel çalışma kitabını içeri aktarma

Kaynak

= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Ürünler tabloyu seçin

Git

= Source{[Item="Products",Kind="Table"]}[Data]

Power Query sütun veri türlerini otomatik olarak algılar

Değiştirilen Tür

= Table.TransformColumnTypes(Products_Table,{{"ÜrünKimlik", Int64.Type}, {"ÜrünAdı", text}, {"TedarikçiKimlik", Int64.Type}, {"CategoryID", Int64.Type}, {"MiktarPerUnit", text}, {"BirimFiyatı", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})

Yalnızca ilgili sütunları görüntülemek için diğer sütunları kaldırma

Diğer sütunlar kaldırıldı

= Table.SelectColumns(FirstRowAsHeader,{"ÜrünKimliği", "ÜrünAdı", "KategoriKimliği", "MiktarPerunit"})

Bu görevde, verileri http://services.odata.org/Northwind/Northwind.svc'daki örnek Northwind OData akışından Excel çalışma kitabınıza aktaracak, Order_Detailstablosuna genişletecek, sütunları kaldıracak, satır toplamını hesapleyecek, Sipariş Alanı'nı dönüştürecek, satırları ÜrünKimlik ve Yıl değerlerine göre grupleyecek, sorguyu yeniden adlandıracak ve Excel çalışma kitabına sorgu indirmeyi devre dışı bırakacaksınız.

1. Adım: OData Akışına bağlanma

  1. OData >Veri Kaynağından> Veri Kaynağı Al > öğesini seçin.

  2. OData Akışı iletişim kutusunda, Northwind OData akışı için URL girin.

  3. Tamam’ı seçin.

  4. Gezgin bölmesinde Siparişler tablosuna çift tıklayın.

Adım 2: Sipariş_Ayrıntıları tablosunu genişletme

Bu adımda, Sipariş_Ayrıntıları tablosunun ÜrünKimliği, BirimFiyat ve Miktar sütunlarını Siparişler tablosuyla birleştirmek için, Siparişler tablosuyla ilişkili Sipariş_Ayrıntıları tablosunu genişletiyorsunuz. Genişlet işlemi, ilişkili tablonun sütunlarını konu tablosuyla bir araya getirir. Sorgu çalıştırılıyorsa, ilişkili tablodan(Order_Details) gelen satırlar, birincil tabloyla (Siparişler) birliktesatırlar halinde bir araya olur.

Power Query'de, ilişkili tablo içeren bir sütunda Kayıt veya Tablo değeri hücrededir. Bunlara yapılandırılmış sütunlar denir. Kayıt, tek bir ilişkili kaydı gösterir ve geçerli verilerle veya birincil tabloyla bire bir ilişkiyi temsil eder. Tablo ilişkili bir tabloyu ve geçerli tabloyla veya birincil tabloyla bire çok ilişkiyi gösterir. Yapılandırılmış sütun ilişkisel modeli olan bir veri kaynağında bir ilişkiyi temsil eder. Örneğin, yapılandırılmış bir sütun OData akışında yabancı anahtar ilişkisine veya SQL Server veritabanında yabancı anahtar ilişkisine sahip bir varlığı gösterir.

Sipariş_Ayrıntıları tablosunu genişlettikten sonra, Siparişler tablosuna üç yeni sütun (iç içe yerleştirilmiş veya ilişkili tablodaki her satır için bir sütun) ve ek satırlar eklenir.

  1. Veri Önizleme'de,yatay olarak yatay olarak sayfa Order_Details kaydırın.

  2. Sütun Order_Details genişletme simgesini ( Veya Genişlet ).

  3. Genişlet açılır listesinde:

    1. Tüm sütunları temizlemek için (Tüm Sütunları Seç) öğesini seçin.

    2. ÜrünKimliği, BirimFiyatıve Miktar'ı seçin.

    3. Tamam’ı seçin.

      Sipariş_Ayrıntıları tablosunu genişletme bağlantısı

      Not: Power Query'de, sütundan bağlantılı tabloları genişletebilirsiniz ve konu tablosunda verileri genişletmeden önce bağlı tablonun sütunlarını toplayabilirsiniz. Toplama işlemlerinin nasıl gerçekleştir olduğu hakkında daha fazla bilgi için bkz. Sütundan verileri toplama.

Adım 3: Yalnızca ilgili sütunları görüntülemek için diğer sütunları kaldırma

Bu adımda SiparişTarihi, ÜrünKimliği, BirimFiyat ve Miktar dışındaki tüm sütunları kaldıralım. 

  1. Veri Önizleme'deaşağıdaki sütunları seçin: 

    1. İlk sütunu (SiparişKimsi) seçin.

    2. Son sütun olan Gönderici'ye Shift tuşunu basılı bulundurarak tıklayın.

    3. Shift tuşunu basılı tutarak SiparişTarihi, Sipariş_Ayrıntıları.ÜrünKimliği, Sipariş_Ayrıntıları.BirimFiyat ve Sipariş_Ayrıntıları.Miktar sütunlarını tıklatın.

  2. Seçili bir sütun başlığına sağ tıklayın ve Diğer Sütunları Kaldır'ı seçin.

Adım 4: Her Sipariş_Ayrıntıları satırı için satır toplamını hesaplama

Bu adımda, her Sipariş_Ayrıntıları satırı için satır toplamını hesaplamak üzere bir Özel Sütun oluşturuyorsunuz.

  1. Veri Önizleme'de,önizlemenin sol Tablo simgesi tablo simgesini ( resim ) seçin.

  2. Özel Sütun Ekle'ye tıklayın.

  3. Özel Sütun iletişim kutusunda, Özel sütun formülü kutusuna [Order_Details.BirimFiyata] * [Order_Details.Miktar] girin.

  4. Yeni sütun adı kutusuna Satır Toplamı girin.

  5. Tamam’ı seçin.

Her Sipariş_Ayrıntıları satırı için satır toplamını hesaplama

Adım 5: SiparişTarihi yıl sütununu dönüştürme

Bu adımda, sipariş tarihinin yılını göstermek için SiparişTarihisütununu dönüştürelim.

  1. Veri Önizleme'deSipariş Adı sütununa sağ tıklayın ve Yıl Olarak Dönüştür'> seçin.

  2. SiparişTarihi sütununu Yıl olarak yeniden adlandırın:

    1. SiparişTarihi sütununu çift tıklatın ve Yıl yazın veya

    2. Right-Click Sipariş Adı sütununda Yeniden Adlandır'ıseçin veYıl girin.

Adım 6: Satırları ÜrünKimliği ve Yıl sütunlarına göre gruplandırma

  1. Veri Önizleme'deYıl ve YılOrder_Details.ÜrünKimlik öğesini seçin.

  2. Right-Click birini seçin ve Group By öğesini seçin.

  3. Gruplandır iletişim kutusunda:

    1. Yeni sütun adı metin kutusunda Toplam Satışlar girin.

    2. İşlem açılan listesinde Toplam’ı seçin.

    3. Sütun açılan listesinde Satır Toplamı’nı seçin.

  4. Tamam’ı seçin.

    Toplama İşlemleri için Gruplandır İletişim Kutusu

Adım 7: Sorguyu yeniden adlandırma

Satış verilerini Excel'e aktarmadan önce sorguyu yeniden adlandırın:

  • Sorgu Ayarları bölmesindeki Ad kutusuna ToplamSatışlar girin.

Sonuçlar: Görev 2 için son sorgu

Her adımı uyguladıktan sonra, Northwind OData akışı üzerinde bir Toplam Satışlar sorgunuz olur.

Toplam Satışlar

Özet: Görev 2'de oluşturulan Power Query adımları 

Power Query'de sorgu etkinlikleri gerçekleştir sonuç olarak, sorgu adımları oluşturulur ve Sorgu Ayarları bölmesindeki Uygulanan Adımlar listesinde listelenir. Her sorgu adımının, "M" dili olarak da bilinen, buna karşılık gelen bir Power Query formülü vardır. Power Query formülleri hakkında daha fazla bilgi için bkz. Power Query formülleri hakkında bilgi.

Görev

Sorgu adımı

Formül

OData akışına bağlanma

Source

= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])

Bir tablo seç

Gezinti

= Source{[Name="Siparişler"]}[Veri]

Sipariş_Ayrıntıları tablosunu genişletme

Sipariş_Ayrıntıları’nı genişletme

= Table.ExpandTableColumn(Siparişler, "Order_Details", {"ÜrünKimliği", "BirimFiyatı", "Miktar"}, {"Order_Details.ÜrünKimliği", "Order_Details.BirimFiyatı", "Order_Details.Miktar"})

Yalnızca ilgili sütunları görüntülemek için diğer sütunları kaldırma

RemovedColumns

= Table.RemoveColumns(#"Expand Order_Details",{"SiparişKimliği", "MüşteriKimliği", "ÇalışanKimliği", "GerekliLikAdı", "NakliyeKimliği", "NakliyeSaat", "Navlun", "NakliyeAdı", "NakliyeAdresi", "NakliyeŞekimliği", "SevkPostaKodu", "NakliyeÜlgesi", "Müşteri", "Çalışan", "Nakliyeci"})

Her Sipariş_Ayrıntıları satırı için satır toplamını hesaplama

Özel eklendi

= Table.AddColumn(RemovedColumns, "Özel", her [Order_Details.BirimFiyata] * [Order_Details.Miktar])

= Table.AddColumn(#"Genişletilmiş Order_Details", "Satır Toplamı", her biri [Order_Details.BirimFiyata] * [Order_Details.Miktar])

Daha anlamlı bir adla Lne Toplam olarak değiştirme

Yeniden Adlandırılmış Sütunlar

= Table.RenameColumns(InsertedCustom,{{"Custom", "Satır Toplamı"}})

Yılı görüntülemek için SiparişTarihi sütununu dönüştürme

Ayıklanan Yıl

= Table.TransformColumns(#"Gruplu Satırlar",{{"Yıl", Tarih.Yıl, Int64.Type}})

Değiştir 

daha anlamlı adlar, Sipariş Adı ve Yıl

Yeniden Adlandırılmış Sütunlar 1

Table.RenameColumns

(TransformedColumn,{{"SiparişTarihi", "Yıl"}})

Satırları ÜrünKimliği ve Yıl sütunlarına göre gruplandırma

GroupedRows

= Table.Group(RenamedColumns1, {"Yıl", "Order_Details.ÜrünKimliği"}, {{"Toplam Satışlar", each List.Sum([Satır Toplamı]), number}})

Power Query, birden çok sorguyu birleştirerek veya ekerek birleştirmeye olanak sağlar. Birleştir işlemi, verilerin geldiği veri kaynağından bağımsız olarak, tablo şeklindeki herhangi bir Power Query sorgusunda gerçekleştirilir. Veri kaynaklarını birleştirme hakkında daha fazla bilgi için bkz. Birden çok sorgu birleştirme.

Bu görevde, Birleştirme sorgusunu ve Genişlet'i kullanarak Ürünler ve Toplam Satışlar sorgularını birleştirin ve ardından Ürüne Göre Toplam Satışlar sorgusunu Excel Veri Modeli'ne yükleyebilirsiniz.

Adım 1: ÜrünKimliği’ni Toplam Satışlar sorgusuyla birleştirme

  1. Excel çalışma kitabında, Ürünler çalışma sayfası sekmesinde Ürünler sorgusuna gidin.

  2. Sorguda bir hücreyi seçin ve sonra Sorgu Birleştirme'>seçin.

  3. Birleştir iletişim kutusunda, birincil tablo olarak Ürünler'i seçin ve birleştirilecek ikincil veya ilgili sorgu olarak Toplam Satışlar'ı seçin. Toplam Satışlar, yeni bir yapılandırılmış sütuna ve bir genişletme simgesine sahip olur.

  4. Toplam Satışlar’ı Ürünler tablosuyla ÜrünKimliği’ne göre eşleştirmek için, Ürünler tablosundan ÜrünKimliği sütununu ve Toplam Satışlar tablosundan Sipariş_Ayrıntıları.ÜrünKimliği sütununu seçin.

  5. Gizlilik Düzeyleri iletişim kutusunda:

    1. Her iki veri kaynağı için de gizlilik yalıtım düzeyiniz olarak Kurumsal değerini seçin.

    2. Kaydet'i seçin.

  6. Tamam’ı seçin.

    Güvenlik Notu: Gizlilik Düzeyleri, kullanıcının özel veya kurumsal olabileceği birden çok veri kaynağından verileri yanlışlıkla birleştirmesini sağlar. Sorguya bağlı olarak, kullanıcı özel veri kaynağından verileri yanlışlıkla kötü amaçlı başka bir veri kaynağına gönderebilir. Power Query her veri kaynağını analiz eder ve tanımlı gizlilik düzeyine göre sınıflır: Genel, Kurumsal ve Özel. Gizlilik Düzeyleri hakkında daha fazla bilgi için bkz. Gizlilik Düzeylerini Ayarlama.

    Birleştir iletişim kutusu

Sonuç

Birleştir işlemi bir sorgu oluşturur. Sorgu sonucu birincil tablonun (Ürünler) tüm sütunlarınıveilişkili tabloya yapılandırılmış tek bir Tablo sütunu (Toplam Satışlar) içerir. İkincil veya ilişkili tablodan birincil tabloya yeni sütunlar eklemek için Genişlet simgesini seçin.

Son Birleştirme

2. Adım: Birleştirilmiş sütunu genişletme

Bu adımda, birleştirilmiş sütunu Yeni Sütun adıyla genişletecek ve Ürünler sorgusunda iki yeni sütun oluşturleyeceksiniz: Yıl ve Toplam Satışlar.

  1. Veri Önizleme'de,NewColumn'unyanındaki Genişlet simgesini ( Genişlet ) seçin.

  2. Genişlet açılan listesinde:

    1. Tüm sütunları temizlemek için (Tüm Sütunları Seç) öğesini seçin.

    2. Yıl ve ToplamSatışlar'ı seçin.

    3. Tamam’ı seçin.

  3. Bu iki sütunu Yıl ve Toplam Satışlar olarak yeniden adlandırın.

  4. Hangi ürünlerde ve hangi yıllarda en yüksek satış hacmine sahip olduğunu bulmak için Toplam Satışlara Göre Azalan Düzende Sırala'yaseçin.

  5. Sorguyu Ürüne Göre Toplam Satış olarak yeniden adlandırın.

Sonuç

Tablo bağlantısını genişletme

Adım 3: Excel Veri Modeli’ne Ürünlere göre Toplam Satışlar sorgusunu yükleme

Bu adımda, sorgu sonucuyla bağlantılı bir rapor oluşturmak için sorguyu ExcelVeri Modeli'ne yükleyebilirsiniz. Verileri Excel Veri Modeli'ne yükledikten sonra,veri çözümlemenizi daha ileri yüklemek için Power Pivot'ı kullanabilirsiniz.

  1. Giriş'i> Yükle'& Kapat'ı seçin.

  2. Veri İçeri Aktar iletişim kutusunda, Bu verileri Veri Modeline ekle'yi seçmeye emin olun. Bu iletişim kutusunu kullanma hakkında daha fazla bilgi için, soru işaretini (?) seçin.

Sonuç

Dosyadan ve Northwind OData akışından gelen verileri Products.xlsx Bir Ürüne göre Toplam Satış sorgunuz var. Bu sorgu bir Power Pivot modeline uygulanır. Buna ek olarak, sorguda yapılan değişiklikler Veri Modelinde sonuçta elde edilen tabloyu değiştirir ve yeniler.

Özet: Görev 3'te oluşturulan Power Query adımları

Power Query'de Birleştirme sorgusu etkinlikleri gerçekleştirinken, sorgu adımları oluşturulur ve Sorgu Ayarları bölmesindeki Uygulanan Adımlar listesinde listelenir. Her sorgu adımının, "M" dili olarak da bilinen, buna karşılık gelen bir Power Query formülü vardır. Power Query formülleri hakkında daha fazla bilgi için bkz. Power Query formülleri hakkında bilgi.

Görev

Sorgu adımı

Formül

ÜrünKimliği’ni Toplam Satışlar sorgusuyla birleştirme

Source (Birleştir işleminin veri kaynağı)

= Table.NestedJoin(Ürünler, {"ÜrünKimlik"}, #"Toplam Satışlar", {"Order_Details.ÜrünKimlik"}, "Toplam Satışlar", JoinKind.LeftOuter)

Birleştirme sütununu genişletme

Genişletilmiş Toplam Satışlar

= Table.ExpandTableColumn(Kaynak, "Toplam Satışlar", {"Yıl", "Toplam Satışlar"}, {"Toplam Satışlar.Yıl", "Toplam Satışlar.Toplam Satışlar"})

İki sütunu yeniden adlandırma

Yeniden Adlandırılmış Sütunlar

= Table.RenameColumns(#"Genişletilmiş Toplam Satışlar",{{"Toplam Satışlar.Yıl", "Yıl"}, {"Toplam Satışlar.Toplam Satışlar", "Toplam Satışlar"}})

Toplam Satışları artan düzende sıralama

Sıralanmış Satırlar

= Table.Sort(#"Yeniden Adlandırılmış Sütunlar",{{"Toplam Satışlar", Sipariş.Artan}})

Ayrıca Bkz:

Excel için Power Query Yardım

Daha fazla yardıma mı ihtiyacınız var?

Office yeteneklerinizi geliştirin
Eğitimleri keşfedin
Yeni özellikleri ilk olarak siz edinin
Office Insider Programına Katılın

Bu bilgi yararlı oldu mu?

Görüşleriniz için teşekkür ederiz!

Geri bildiriminiz için teşekkürler! Office destek temsilcilerimizden biriyle görüşmeniz yararlı olabilir.

×