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

Bu görevde, Ürünler ve Orders.xlsx (yukarıda indirilip yeniden adlandırıldı) dosyasındaki ürünleri bir Excel çalışma kitabına aktaracak, satırları sütun üst bilgilerine yükseltecek, bazı sütunları kaldıracak 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. Veri > Veri Al > Dosyadan > Çalışma Kitabından'ı seçin.

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

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

2. Adım: Sorgu Adımlarını İnceleme

Varsayılan olarak, Power Query size kolaylık sağlamak için otomatik olarak birkaç adım ekler. Daha fazla bilgi edinmek için Sorgu Ayarları bölmesindeki Uygulanan Adımlar altındaki her adımı inceleyin.

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

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ğinizde oluşturulmuştur.

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 çıkaran Power Query tarafından oluşturulmuştur. Formülün tamamını görmek için formül çubuğunun sağındaki 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 ProductID, ProductName, CategoryID ve QuantityPerUnit sütunlarını seçin (Ctrl+Tıklama veya Shift+Tıklama tuşlarını kullanın).

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

   

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

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

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

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

Power Query'de sorgu etkinlikleri gerçekleştirirken, sorgu adımları oluşturulur ve Sorgu Ayarları bölmesinde, Uygulanan Adımlar listesinde listelenir. Her sorgu adımının "M" dili olarak da bilinen 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 tablosunu 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,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", 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,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

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_Details tablosunu genişletecek, sütunları kaldıracak, satır toplamını hesaplayacak, OrderDate'ı dönüştürecek, satırları ProductID ve Year'a göre gruplandıracak, 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. Veri > Diğer Kaynaklardan> Veri Al > OData Akışından'ı 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ıldığında, ilişkili tablodan (Order_Details) satırlar birincil tabloyla (Siparişler) birleştirilir.

Power Query'da, ilişkili tablo içeren bir sütun hücrede Record veya Table değerine sahiptir. Bunlar yapılandırılmış sütunlar olarak adlandırılır. Kayıt, ilgili tek bir kaydı gösterir ve geçerli verilerle veya birincil tabloyla bire birilişkiyi temsil eder. Tablo, ilişkili bir tabloyu gösterir ve geçerli veya birincil tabloyla bire çok ilişkiyi temsil eder. Yapılandırılmış sütun, ilişkisel modeli olan bir veri kaynağındaki ilişkiyi temsil eder. Örneğin, yapılandırılmış 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 Order_Details sütununa kaydırın.

2. Order_Details sütununda genişlet simgesini () seçin.

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

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

   2. ProductID, UnitPrice ve Quantity'ı seçin.

   3. Tamam’ı seçin.

      

      Not: Power Query'da, bir sütundan bağlantılı tabloları genişletebilir ve konu tablosundaki verileri genişletmeden önce bağlantılı tablonun sütunlarını toplayabilirsiniz. Toplama işlemlerini gerçekleştirme hakkında daha fazla bilgi için bkz. Sütundan veri 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'de aşağıdaki sütunları seçin: 

   1. İlk sütun olan OrderID'yi seçin.

   2. Shift tuşunu basılı tutarak son sütun olan Nakliyeci'ye 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 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 üst köşesindeki tablo simgesini () seçin.

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

3. Özel Sütun iletişim kutusundaki Özel sütun formülü kutusuna [Order_Details.UnitPrice] * [Order_Details.Quantity] girin.

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

5. Tamam’ı seçin.

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'de OrderDate sütununa sağ tıklayın ve > YılDö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. OrderDate sütununda Right-Click Yeniden Adlandır'ı seçin ve Yıl yazın.

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

1. Veri Önizleme'de Year ve Order_Details.ProductID'yi seçin.

2. Üst bilgilerden birini Right-Click ve Gruplandırma Ölçütü'ne tıklayın.

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.

   

Adım 7: Sorguyu yeniden adlandırma

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

• Sorgu Ayarları bölmesindeki Ad kutusuna Toplam Satışlar yazın.

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.

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

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

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="Orders"]}[Data]
Sipariş_Ayrıntıları tablosunu genişletme	Sipariş_Ayrıntıları’nı genişletme	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Yalnızca ilgili sütunları görüntülemek için diğer sütunları kaldırma	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Her Sipariş_Ayrıntıları satırı için satır toplamını hesaplama	Özel eklendi	= Table.AddColumn(RemovedColumns, "Custom", her biri [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Genişletilmiş Order_Details", "Satır Toplamı", her biri [Order_Details.BirimFiyat] * [Order_Details.Miktar])
Daha anlamlı bir adla (Lne Total) değiştirin	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(#"Gruplandırılmış Satırlar",{{"Yıl", Date.Year, Int64.Type}})
Değiştir ve  daha anlamlı adlar, OrderDate ve Year	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, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Power Query birleştirerek veya ekleyerek birden çok sorgu birleştirmenizi sağlar. Birleştirme 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ştir sorgusu ve Genişlet işlemini kullanarak Ürünler ve Toplam Satış sorgularını birleştirir ve ardından Ürün Başına Toplam Satış sorgusunu Excel Veri Modeli'ne yüklersiniz.

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

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

2. Sorguda bir hücre seçin ve ardından Sorgu > Birleştir'i 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ış'ı seçin. Toplam Satış, genişlet simgesine sahip yeni bir yapılandırılmış sütun haline gelir.

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 yanlışlıkla özel veya kurumsal olabilecek birden çok veri kaynağındaki verileri birleştirmesini engeller. Sorguya bağlı olarak, kullanıcı yanlışlıkla özel veri kaynağından kötü amaçlı olabilecek başka bir veri kaynağına veri gönderebilir. Power Query her veri kaynağını analiz eder ve bunu tanımlı gizlilik düzeyine sınıflandırır: Genel, Kurumsal ve Özel. Gizlilik Düzeyleri hakkında daha fazla bilgi için bkz. Gizlilik Düzeylerini Ayarlama.

   

Sonuç

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

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

Bu adımda, Ürünler sorgusunda iki yeni sütun oluşturmak için NewColumn adlı birleştirilmiş sütunu genişletirsiniz: Year ve Total Sales.

1. Veri Önizleme'de , YeniSütun'un yanındaki Genişlet simgesi () öğesini 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 Toplam Satış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ünleri ve hangi yıllarda ürünlerin en yüksek satış hacmine sahip olduğunu öğrenmek için Azalan Düzende Toplam SatışaGöre Sırala'yı seçin.

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

Sonuç

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

Bu adımda, sorgu sonucuna bağlı bir rapor oluşturmak için excel veri modeline sorgu yüklersiniz. Verileri Excel Veri Modeli'ne yükledikten sonra, veri analizinizi daha ileriye gitmek için Power Pivot'ı kullanabilirsiniz.

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

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

Sonuç

Products.xlsx dosyasındaki ve Northwind OData akışındaki verileri birleştiren Bir Ürün Başına Toplam Satış sorgunuz var. Bu sorgu bir Power Pivot modeline uygulanır. Ayrıca, sorguda yapılan değişiklikler Veri Modeli'nde elde edilen tabloyu değiştirir ve yeniler.

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

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

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(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Birleştirme sütununu genişletme	Genişletilmiş Toplam Satışlar	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
İki sütunu yeniden adlandırma	Yeniden Adlandırılmış Sütunlar	= Table.RenameColumns(#"Genişletilmiş Toplam Satışlar",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Toplam Satışları artan düzende sırala	Sıralanmış Satırlar	= Table.Sort(#"Yeniden Adlandırılmış Sütunlar",{{"Toplam Satışlar", Order.Ascending}})