Bu öğreticide, Power Query Sorgu Düzenleyicisi 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ştirip her iki kaynaktan gelen verileri bir araya getirerek bir "Ürün ve Yıllara göre Toplam Satışlar" raporu oluşturacaksınız.
Bu öğreticiyi gerçekleştirmek için Ürünler çalışma kitabına ihtiyacınız vardır. Farklı Kaydet iletişim kutusunda, dosyayı Ürünler ve Siparişler.xlsx olarak adlandırın.
Görev 1: Ürünleri Excel çalışma kitabına aktarma
Bu görevde, Ürünler ve Orders.xlsx (yukarıda indirilmiş ve yeniden adlandırılmıştır) dosyasındaki ürünleri Excel çalışma kitabına aktarıyor, satırları sütun başlıkları düzeyine yükseltiyor, bazı sütunları kaldırıyor ve sorguyu bir çalışma sayfasına yüklüyorsunuz.
Adım 1: Excel çalışma kitabına bağlanma
- Bir Excel çalışma kitabı oluşturun.
- Veri'yi> seçin,çalışma kitabından,dosyadan>veri> alın.
- Verileri İçeri Aktar iletişim kutusunda, indirdiğiniz Products.xlsx dosyasını bulup bulun ve ardından Aç'ı seçin.
- Gezgin bölmesinde Ürünler tablosuna çift tıklayın. Power Query Düzenleyicisi görüntülenir.
Adım 2: Sorgu adımlarını inceleme
Varsayılan olarak, Power QueryPower Query size kolaylık sağlamak için birkaç adımı otomatik olarak ekler. Daha fazla bilgi edinmek için Sorgu Ayarları bölmesindeki Uygulanan Adımlar altında her adımı inceleyin.
- 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.
- Gezinti adımına sağ tıklayın ve Ayarları Düzenle'yi seçin. Bu adım, Gezinti iletişim kutusundan tablo seçtiğinizde oluşturuldu.
- 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 çıkarsayan 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.
- Veri Önizleme'deÜrünKimliği, ÜrünAdı, KategoriKimliği ve BirimBaşına Miktar sütunlarını seçin (Ctrl+Tıklama veya Shift+Tıklama tuşlarını kullanın).
-
Sütunları>Kaldır'ı ve diğer sütunları kaldırmayı seçin.
4. Adım: Ürünler sorgusunu yükleme
Bu adımda, Ürünler sorgusunu bir Excel çalışma sayfasına yüklüyorsunuz.
- Giriş>,Kapat & Yükle'yi 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'da sorgu işlemleri yaptığınızda, 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, ilgili 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:\Ürünler ve Orders.xlsx"), null, true) |
| Ürünler tablosunu seçin | Git | = Kaynak{[Öğe="Ürünler",Tür="Tablo"]}[Veri] |
| Power QueryPower Query sütun veri türlerini otomatik olarak algılar | Tür değiştirildi | = 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,{"ÜrünKimliği", "ÜrünAdı", "KategoriKimliği", "ÜrünMiktarı"}) |
Görev 2: OData akışından sipariş verilerini içeri aktarma
Bu görevde, http://services.odata.org/Northwind/Northwind.svc'daki örnek Northwind OData akışından verileri Excel çalışma kitabınıza aktarabilir, Order_Details tablosunu genişletebilir, sütunları kaldırabilir, satır toplamını hesaplayabilir, SiparişTarihi işlevini dönüştürebilir, satırları ÜrünKimliği ve Yıla göre gruplandırabilir, sorguyu yeniden adlandırabilir ve Excel çalışma kitabına sorgu indirme işlemini devre dışı bırakabilirsiniz.
Adım 1: OData akışına bağlanma
- Veriler'i> seçin,OData akışındandiğer kaynaklardan>veri> alın.
- OData Akışı iletişim kutusunda, Northwind OData akışı için URL girin.
- Tamam’ı seçin.
- Gezgin bölmesinde Siparişler tablosunu çift tıklatın.
2. Adım: Order_Details 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) gelen satırlar, birincil tabloyla (Siparişler) birlikte bir araya getirilir.
Power Query'da, ilişkili tablo içeren bir sütunun hücresinde Kayıt veya Tablo değeri vardır. Bunlar, yapılandırılmış sütunlar olarak adlandırılır. 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 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ış bir sütun, bir OData akışında yabancı anahtar ilişkilendirmesi veya bir SQL Server SQL Server veritabanında yabancı anahtar ilişkisi olan bir varlığı gösterir.
Order_Details 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.
Veri Önizleme'de, yatay olarak Order_Details sütuna kaydırın.
Order_Details sütunda, genişlet simgesini (
) seçin.Genişlet açılır listesinde:
Tüm sütunları temizlemek için seçin (Tüm Sütunları Seç).
Ürün Kimliği, BirimFiyat ve Miktar öğelerini seçin.
Tamam’ı seçin.
Not
Power Query'de, 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şlemlerinin nasıl yapıldığı 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.
Veri Önizleme'de aşağıdaki sütunları seçin:
- İlk sütun olan SiparişNo'yu seçin.
- Shift+Son sütuna, Nakliyeci'ye tıklayın.
- 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.
Seçili sütun başlığına sağ tıklayın ve Diğer Sütunları Kaldır'ı seçin.
Adım 4: Her Order_Details 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.
-
Veri Önizleme'de, önizlemenin sol üst köşesindeki tablo simgesini (
) seçin. - Özel Sütun Ekle'yi tıklatın.
- Özel Sütun iletişim kutusunda, Özel sütun formülü kutusuna [Order_Details.BirimFiyat] * [Order_Details.Miktar] girin.
- Yeni sütun adı kutusuna Satır Toplamı'nı girin.
- 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.
Veri Önizleme'deSiparişTarihi sütununa sağ tıklayın ve Dönüşüm Yılı'nı> seçin.
SiparişTarihi sütununu Yıl olarak yeniden adlandırın:
- SiparişTarihi sütununu çift tıklatın ve Yıl yazın veya
- Right-Click SiparişTarihi sütununda Yeniden Adlandır'ı seçin ve Yıl girin.
Adım 6: Satırları ÜrünKimliği ve Yıl sütunlarına göre gruplandırma
Veri Önizlemesi'ndeYear ve Order_Details.ProductID öğelerini seçin.
Üst bilgilerden birine Right-Click ve Gruplandırma Ölçütü'nü seçin.
Gruplandır iletişim kutusunda:
- Yeni sütun adı metin kutusunda Toplam Satışlar girin.
- İşlem açılan listesinde Toplam’ı seçin.
- Sütun açılan listesinde Satır Toplamı’nı seçin.
Tamam’ı seçin.
Adım 7: Sorguyu yeniden adlandırma
Satış verilerini Excel'e aktarmadan önce, sorguyu yeniden adlandırın:
- Sorgu Ayarları bölmesinde, Ad kutusuna Toplam Satış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.
Özet: Görev 2'de oluşturulan Power Query adımları
Power Query'da sorgu işlemleri yaptığınızda, 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, ilgili 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 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 | = Kaynak{[İsim="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",{"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, "Özel", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Genişletilmiş Order_Details", "Satır Toplamı", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
| Daha anlamlı bir ad olan Lne Total ile değiştir | Yeniden adlandırılan sütunlar | = Table.RenameColumns(InsertedCustom,{{"Özel", "Satır Toplamı"}}) |
| Yılı görüntülemek için SiparişTarihi sütununu dönüştürme | Çıkarıldığı Yıl | = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Değiştir ve daha anlamlı adlar, SiparişTarihi ve Yıl |
Yeniden adlandırılan 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ı]), type number}}) |
Görev 3: Ürünler ve Toplam Satışlar sorgularını bir araya getirme
Power QueryPower Query, birden çok sorguyu birleştirerek veya sonuna ekleyerek birleştirmenize olanak tanır. Birleştirme işlemi, verilerin geldiği veri kaynağından bağımsız olarak, tablo şeklindeki herhangi bir Power Query sorgusu üzerinde gerçekleştirilir. Veri kaynaklarını bir araya getirme hakkında daha fazla bilgi için bkz. Birden çok sorguyu bir araya getirme.
Bu görevde, Birleştirmesorgusu ve Genişletme işlemini kullanarak Ürünler ve Toplam Satışlar sorgularını birleştiriyor ve ardından Ürünlere göre Toplam Satışlar sorgusunu Excel Veri Modeli'ne yüklüyorsunuz.
Adım 1: ÜrünKimliği'ni Toplam Satışlar sorgusuyla birleştirme
Excel çalışma kitabında, Ürünler çalışma sayfası sekmesindeki Ürünler sorgusuna gidin.
Sorguda bir hücreyi seçtikten sonra Sorgu Birleştirme'yi> seçin.
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 , genişletme simgesiyle yeni yapılandırılmış bir sütuna dönüşür.
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.
Gizlilik Düzeyleri iletişim kutusunda:
- Her iki veri kaynağı için de gizlilik yalıtım düzeyiniz olarak Kurumsal değerini seçin.
- Kaydet'i seçin.
Tamam’ı seçin.
Not
Gizlilik Düzeyleri , kullanıcının özel veya kurumsal olabilecek birden çok veri kaynağından verileri yanlışlıkla bir araya getirmesini engeller. Sorguya bağlı olarak, kullanıcı özel veri kaynağından alınan verileri yanlışlıkla kötü amaçlı olabilecek başka bir veri kaynağına gönderebilir. Power QueryPower Query her veri kaynağını analiz eder ve tanımlanan gizlilik düzeyine göre 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 tablonun (Ürünler) tüm sütunlarını ve ilişkili tablonun (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.
Adım 2: Birleştirilmiş sütunu genişletme
Bu adımda, Ürünler sorgusunda Yıl veToplam Satışlar olmak üzere iki yeni sütun oluşturmak için, YeniSütun adlı birleştirilmiş sütunu genişletiyorsunuz.
Veri Önizleme'de, YeniSütun'un yanındaki Genişlet simgesini (
) seçin.Genişlet açılan listesinde:
- Tüm sütunları temizlemek için seçin (Tüm Sütunları Seç).
- Yıl ve Toplam Satışlar'ı seçin.
- Tamam’ı seçin.
Bu iki sütunu Yıl ve Toplam Satışlar olarak yeniden adlandırın.
Hangi ürünlerin hangi yıllarda en yüksek satış hacmine sahip olduğunu bulmak için Toplam Satışlara göre Azalan Düzende Sırala'yı seçin.
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 sonucuyla bağlantılı bir rapor oluşturmak için sorguyu Excel Veri Modeli'ne yüklersiniz. Verileri Excel Veri Modeli'ne yükledikten sonra, veri çözümlemenizi ilerletmek için Power Pivot'u kullanabilirsiniz.
- Giriş>,Kapat & Yükle'yi seçin.
- Verileri İçeri Aktar iletişim kutusunda Bu veriyi Veri Modeli'ne 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ından ve Northwind OData akışından gelen verileri bir araya getiren Ürünlere göre Toplam Satış sorgunuz var. Bu sorgu Power Pivot modeline uygulanır. Buna ek olarak, sorguda yapılan değişiklikler Veri Modeli'ndeki sonuç tablosunu 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ş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, ilgili 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 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, {"ÜrünKimliği"}, #"Toplam Satışlar", {"Order_Details.ÜrünKimliği"}, "Toplam Satışlar", JoinKind.LeftOuter) |
| Birleştirme sütununu genişletme | Genişletilmiş Toplam Satışlar | = Table.ExpandTableColumn(Source, "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ılan 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"}}) |
| Satışları toplamı artan düzende sırala | Sıralanmış Satırlar | = Table.Sort(#"Yeniden Adlandırılmış Sütunlar",{{"Toplam Satışlar", Order.Ascending}}) |