Excel’de Power Query formülleri oluşturma

Power Query Düzenleyicisi, Excel için birçok veri kaynağından verileri yeniden şekillendirmek için kullanabileceğiniz bir veri sorgusu ve şekillendirme deneyimi sağlar. Power Query Düzenleyicisi penceresini görüntülemek için, Excel çalışma sayfasındaki dış veri kaynaklarından verileri içeri aktarın, verilerde bir hücre seçin ve ardından Sorgu > Düzenle'yi seçin. Aşağıda ana bileşenlerin bir özeti yer alır.

1. Verilerinizi şekillendirmek için kullandığınız Power Query Düzenleyicisi şeridi

2. Veri kaynaklarını ve tabloları bulmak için kullandığınız Sorgular bölmesi

3. Şeritteki komutların kullanışlı kısayolları olan bağlam menüleri

4. Verilere uygulanan adımların sonuçlarını görüntüleyen Veri Önizlemesi

5. Özellikleri ve sorgudaki her adımı listeleyen Sorgu Ayarları bölmesi

Arka planda, sorgudaki her adım, formül çubuğunda görünen bir formülü temel alır.

Bazen formülü değiştirmek veya oluşturmak isteyebilirsiniz. Formüller, hem basit hem de karmaşık ifadeler oluşturmak için kullanabileceğiniz Power Query Formül Dili'ni kullanır. Söz dizimi, bağımsız değişkenler, açıklamalar, işlevler ve örnekler hakkında daha fazla bilgi için bkz. M formül dilini Power Query.

Örnek olarak futbol şampiyonası listesini kullanarak Power Query kullanarak bir web sitesinde bulduğunuz ham verileri alıp iyi biçimlendirilmiş bir tabloya dönüştürün. Sorgu Ayarları bölmesinde, Uygulanan Adımlar altında ve Formül çubuğunda her görev için sorgu adımlarının ve karşılık gelen formüllerin nasıl oluşturulduğunu izleyin.

Yöntem

1. Verileri içeri aktarmak için Veri > Web'den'i seçin, URL kutusuna "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" yazın ve tamam'ı seçin.

2. Gezgin iletişim kutusunda, soldaki Sonuçlar [Düzenle] tablosunu seçin ve ardından alt kısımdaki Verileri Dönüştür'ü seçin. Power Query düzenleyicisi görüntülenir.

3. Varsayılan sorgu adını değiştirmek için , Sorgu Ayarları bölmesindeki Özellikler'in altında "Sonuçlar [Düzenle]" öğesini silin ve "UEFA şampiyonları" yazın.

4. İstenmeyen sütunları kaldırmak için birinci, dördüncü ve beşinci sütunları seçin ve ardından Giriş > Sütunu Kaldır > Diğer Sütunları Kaldır'ı seçin.

5. İstenmeyen değerleri kaldırmak için Sütun1'i seçin, Giriş > Değerleri Değiştir'i seçin, Bulunacak Değerler kutusuna "ayrıntılar" yazın ve ardından Tamam'ı seçin.

6. "Yıl" sözcüğünü içeren satırları kaldırmak için Sütun1'deki filtre okunu seçin, "Yıl" öğesinin yanındaki onay kutusunu temizleyin ve ardından Tamam'ı seçin.

7. Sütun başlıklarını yeniden adlandırmak için, her birine çift tıklayın ve "Sütun1" öğesini "Yıl", "Sütun4" öğesini "Kazanan" ve "Sütun5" öğesini "Final Score" olarak değiştirin.

8. Sorguyu kaydetmek için Giriş > & Yükle'yi kapat'ı seçin.

Sonuç

Aşağıdaki tabloda uygulanan her adımın ve ilgili formülün özeti yer alır.

Sorgu adımı ve görevi	Formül
Kaynak Web veri kaynağına bağlanma	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Gezinti Bağlanacak tabloyu seçin	=Source{2}[Data]
Değiştirilen Tür Veri türlerini değiştirme (Power Query otomatik olarak yapar)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Diğer Sütunlar Kaldırıldı Yalnızca ilgili sütunları görüntülemek için diğer sütunları kaldırma	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Değiştirilen Değer Seçili sütundaki değerleri temizlemek için değerleri değiştirme	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrelenmiş Satırlar Sütundaki değerleri filtreleme	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Yeniden Adlandırılmış Sütunlar Sütun üst bilgileri anlamlı olacak şekilde değiştirildi	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Önemli    Veri kaynağını tanımlamak ve ayarlamak için Power Query tarafından oluşturulduğundan Kaynak,  Gezinti ve Değiştirilen Tür adımlarını düzenlerken dikkatli olun.

Formül çubuğunu gösterme veya gizleme

Formül çubuğu varsayılan olarak gösterilir, ancak görünmüyorsa yeniden görüntüleyebilirsiniz.

• Formül Çubuğu> Düzen > Görüntüle'yi seçin.

Formülçubuğuna formül ekleme

1. Sorguyu açmak için daha önce Power Query Düzenleyicisi yüklenmiş bir sorguyu bulun, verilerde bir hücre seçin ve ardından Sorgu > Düzenle'yi seçin. Daha fazla bilgi için bkz. Excel'de sorgu oluşturma, yükleme veya düzenleme.

2. Sorgu Ayarları bölmesindeki Uygulanan Adımlar'ın altında düzenlemek istediğiniz adımı seçin.

3. Formül çubuğunda parametre değerlerini bulup değiştirin ve enter simgesini seçin veya Enter tuşuna basın. Örneğin, bu formülü Column2:
   
   Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})
   olarak da değiştirin

4. Veri Önizlemesi'nde görüntülenen yeni sonuçları görmek için Enter simgesini seçin veya Enter tuşuna basın.

5. Excel çalışma sayfasında sonucu görmek için Giriş > Yükle & Kapat'ı seçin.

Formül çubuğunda formül oluşturma

Basit bir formül örneği için Text.Properişlevini kullanarak bir metin değerini uygun büyük/küçük harfe dönüştürelim.

1. Boş bir sorgu açmak için Excel'de Veri > Veri Al > Diğer Kaynaklardan > Boş Sorgu'yu seçin. Daha fazla bilgi için bkz. Excel'de sorgu oluşturma, yükleme veya düzenleme.

2. Formül çubuğuna=Text.Proper("text value")yazın ve enter simgesini seçin veya Enter tuşuna basın.
   
   Sonuçlar Veri Önizlemesi'nde görüntülenir.

3. Excel çalışma sayfasında sonucu görmek için Giriş > Yükle & Kapat'ı seçin.

Sonuç:

 Formül oluşturduğunuzda, Power Query formül söz dizimini doğrular. Ancak, bir sorguya ara adım eklediğinizde, yeniden sıraladığınızda veya sildiğinizde, bir sorguyu kesebilirsiniz.  Sonuçları her zaman Veri Önizlemesi'nde doğrulayın.

Önemli    Veri kaynağını tanımlamak ve ayarlamak için Power Query tarafından oluşturulduğundan Kaynak,  Gezinti ve Değiştirilen Tür adımlarını düzenlerken dikkatli olun.

İletişim kutusu kullanarak formülü düzenleme

Bu yöntem, adıma bağlı olarak değişen iletişim kutularını kullanır. Formülün söz dizimini bilmeniz gerekmez.

1. Sorguyu açmak için daha önce Power Query Düzenleyicisi yüklenmiş bir sorguyu bulun, verilerde bir hücre seçin ve ardından Sorgu > Düzenle'yi seçin. Daha fazla bilgi için bkz. Excel'de sorgu oluşturma, yükleme veya düzenleme.

2. Sorgu Ayarları bölmesinde, Uygulanan Adımlar'ın altında, düzenlemek istediğiniz adımın Ayarları Düzenle simgesini seçin veya adıma sağ tıklayın ve ardından Ayarları Düzenle'yi seçin.

3. İletişim kutusunda değişikliklerinizi yapın ve tamam'ı seçin.

Adım ekleme

Verilerinizi yeniden şekillendiren bir sorgu adımını tamamladıktan sonra, geçerli sorgu adımının altına bir sorgu adımı eklenir. ancak adımların ortasına bir sorgu adımı eklediğinizde, sonraki adımlarda bir hata oluşabilir. Power Query, yeni bir adım eklemeyi denediğinizde Adım Ekle uyarısı görüntüler ve yeni adım, eklenen adımı izleyen adımlardan herhangi birinde kullanılan sütun adları gibi alanları değiştirir.

1. Sorgu Ayarları bölmesindeki Uygulanan Adımlar'ın altında, yeni adımdan hemen önce ve buna karşılık gelen formülden önce olmasını istediğiniz adımı seçin.

2. Formül çubuğunun sol kısmındaki Adım Ekle simgesini seçin. Alternatif olarak, bir adıma sağ tıklayıp Ardından Adım Ekle'yi seçin. biçiminde yeni bir formül oluşturulur:
   
   = <nameOfTheStepToReference>, örneğin =Production.WorkOrder.

3. Şu biçimi kullanarak yeni formülü yazın:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Örneğin, Gender sütununu içeren bir tablonuz olduğunu ve "Ms" değerine sahip bir sütun eklemek istediğinizi varsayalım. veya "Bay", kişinin cinsiyeti bağlı olarak. Formül şöyle olacaktır:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Adımı yeniden sıralama

• Uygulanan Adımlar'ın altındaki Sorgu Ayarları bölmesinde, adıma sağ tıklayın ve ardından Yukarı Taşı veya Aşağı Taşı'yı seçin.

Adım silme

• Adımın sol kısmındaki Sil simgesini seçin veya adıma sağ tıklayın ve ardından Sil veya Sona Kadar Sil'i seçin. Sil simgesi formül çubuğunun solunda da kullanılabilir.

Bu örnekte, Gelişmiş Düzenleyici formüllerin bir bileşimini kullanarak bir sütundaki metni uygun büyük/küçük harfe dönüştürelim. 

Örneğin, uygun büyük/küçük harfe dönüştürmek istediğiniz ProductName sütununa sahip Orders adlı bir Excel tablonuz var. 

Önce:

Sonra:

Gelişmiş sorgu oluşturduğunuzda, let ifadesini temel alan bir dizi sorgu formülü adımı oluşturursunuz. Adları atamak ve ardından Adım'ı tanımlayan in yan tümcesi tarafından başvurulan değerleri hesaplamak için let ifadesini kullanın. Bu örnek, "Formül çubuğunda formül oluşturma" bölümündekiyle aynı sonucu döndürür.

let  
    Source = Text.Proper("hello world")
in  
    Source  

Adım adım ada başvurarak her adımın önceki bir adıma dayandığını göreceksiniz. Power Query Formül Dili büyük/küçük harfe duyarlıdır.

1. Aşama: Gelişmiş Düzenleyici açma

1. Excel'de Veri > Veri Al > Diğer Kaynaklar > Boş Sorgu'yu seçin. Daha fazla bilgi için bkz. Excel'de sorgu oluşturma, yükleme veya düzenleme.

2. Power Query Düzenleyicisi Giriş > Gelişmiş Düzenleyici'ı seçin. Bu seçenek let ifadesinin bir şablonuyla açılır.

2. Aşama: Veri kaynağını tanımlama

1. Aşağıdaki gibi Excel.CurrentWorkbook işlevini kullanarak let ifadesini oluşturun:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]#x2
   
       
   
   Source#x4

2. Sorguyu bir çalışma sayfasına yüklemek için Bitti'yi seçin ve ardından Giriş > Kapat & Yükle'yi > & Yükleme'yi Kapat'ı seçin.

Sonuç:

3. Aşama: İlk satırı üst bilgiler olarak yükseltme

1. Sorguyu açmak için, çalışma sayfasından verilerde bir hücre seçin ve ardından Sorgu > Düzenle'yi seçin. Daha fazla bilgi için bkz. Excel'de sorgu oluşturma, yükleme veya düzenleme (Power Query).

2. Power Query Düzenleyicisi Giriş > Gelişmiş Düzenleyici'ı seçin. Bu, 2. Aşama: Veri kaynağını tanımlama bölümünde oluşturduğunuz deyimle açılır.

3. let ifadesinde ,#"Üst Bilgi Olarak İlk Satır" ve Table.PromoteHeaders işlevlerini şu şekilde ekleyin:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. Sorguyu bir çalışma sayfasına yüklemek için Bitti'yi seçin ve ardından Giriş > Kapat & Yükle'yi > & Yükleme'yi Kapat'ı seçin.

Sonuç:

4. Aşama: Sütundaki her değeri uygun büyük/küçük harf olarak değiştirme

1. Sorguyu açmak için, çalışma sayfasından verilerde bir hücre seçin ve ardından Sorgu > Düzenle'yi seçin. Daha fazla bilgi için bkz. Excel'de sorgu oluşturma, yükleme veya düzenleme.

2. Power Query Düzenleyicisi Giriş > Gelişmiş Düzenleyici'ı seçin. Bu, 3. Aşama: İlk satırı üst bilgiler olarak yükseltin.

3. let ifadesinde Table.TransformColumns işlevini kullanarak her ProductName sütun değerini uygun metne dönüştürün. Önceki "Üst Bilgi Olarak İlk Satır" sorgu formülü adımına başvuruda bulunarak, veri kaynağına #"Her Word Büyük Harfle Yazıldı" ifadesini ekleyin ve sonuç olarak #"Büyük Harfle Her Word" değerini atayın.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Sorguyu bir çalışma sayfasına yüklemek için Bitti'yi seçin ve ardından Giriş > Kapat & Yükle'yi > & Yükleme'yi Kapat'ı seçin.

Sonuç:

Tüm çalışma kitaplarınızın Power Query Düzenleyicisi formül çubuğunun davranışını denetleyebilirsiniz.

Formül çubuğunu görüntüleme veya gizleme

1. Sorgu Seçenekleri > Dosya > Seçenekleri ve Ayarları'nı seçin.

2. Sol bölmedeki GENEL'in altında Power Query Düzenleyicisi'ı seçin.

3. Sağ bölmedeki Düzen'in altında Formül Çubuğunu Görüntüle'yi seçin veya temizleyin.

M IntelliSense'i açma veya kapatma

1. Dosya > Seçenekleri ve Ayarlar > Sorgu Seçenekleri'ni seçin.

2. Sol bölmedeki GENEL'in altında Power Query Düzenleyicisi'ı seçin.

3. Sağ bölmedeki Formül altında formül çubuğunda, gelişmiş düzenleyicide ve özel sütun iletişim kutusunda M Intellisense'i etkinleştir'i seçin veya temizleyin.

Not    Bu ayarın değiştirilmesi, Power Query Düzenleyicisi penceresini bir sonraki açışınızda geçerlilik kazanır.