Canlı bir Power Excel semineri yaptığımda, odadaki herhangi birinin tuhaf bir Excel sorunu varsa, yardım için bana göndermesini öneriyorum. Bu veri temizleme problemini böyle almaya başladım. Birisinin şuna benzeyen bir özet çalışma sayfası vardı:
Verileri şöyle görünecek şekilde yeniden biçimlendirmek istediler:
Bu verilerle ilgili ilginç bir ipucu: G4'teki 18, H4: K4'ün bir alt toplamı gibi görünüyor. G, L vb. Sütunlarını kaldırmak cazip gelebilir, ancak önce çalışan adını G3, L3 ve benzerlerinden çıkarmanız gerekir.
Video kaydediciyi açtığımda ve sorunu çözmek için Power Query'de bazı hantal adımlar kaydettiğimde 9 Şubat Pazar günü 04:00 idi. Normalde video yapmadığım bir gün olan Pazar günü olduğu için, insanlardan sorunun nasıl çözüleceğine dair fikirlerini göndermelerini istedim. 29 çözüm gönderildi.
Her çözüm, sürecime göre bazı yeni ve harika gelişmeler sunuyor. Planım, yöntemimdeki çeşitli iyileştirmeleri gösteren bir dizi makale başlatmaktır.
Video izle
Bu işleme başlamadan önce sizi çözümümü görmeye davet ediyorum:
Ve Power Query'nin benim için oluşturduğu M kodu:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"Çözümlere geçmeden önce, birçok yaygın yorumu ele alalım:
- Bazılarınız, verilerin neden bu formatta göründüğünü anlamak için geriye gideceğinizi söyledi. Bu yorumları takdir ediyorum. Bunun benden daha iyi bir insan olduğunu söyleyen herkes. Yıllar içinde "Neden?" Diye sorduğunuzda öğrendim. cevap genellikle bu yola 17 yıl önce başlayan bu eski çalışanı içerir ve artık hepimiz buna alıştığımız için herkes onu bu şekilde kullanmaya devam ediyor.
- Ayrıca - çoğunuz - nihai çözümün uzun bir dikey masa olması ve ardından nihai sonuçları elde etmek için bir pivot tablo kullanılması gerektiğini söylediniz. Jonathan Cooper bunu en iyi şekilde özetledi: "Ayrıca, uygun bir veri kümesinin" Toplamlar "a sahip olmayacağı ve sonunda özetlenmesi gerekmeyeceği şeklindeki diğer bazı YouTube yorumlarına da katılıyorum. Ancak, kullanıcı gerçekten sade bir eski masa sonra onlara istediklerini verirsin. " Aslında bunun her iki tarafını da görebiliyorum. Bir pivot tabloyu seviyorum ve Power Query'den daha eğlenceli olan tek şey, üstünde güzel bir pivot tablo bulunan Power Query. Ancak her şeyi Power Query'de yapabilirsek, kırılacak bir şey daha az olur.
İşte Çeşitli Tekniklere Köprüler
-
Power Query Teknikleri
- Kayıt Gruplarını Numaralandırma
- Soldaki İki Karakteri Çıkarma
- Toplam Sütun
- Aksi takdirde Cümleler
- Power Query'de Birden Çok Özdeş Üstbilgi
- Ne Silinmeli
- Q ile Böl
- Satır Öğelerini Sıralama
- Excel MVP'lerinden Power Query Çözümleri
-
Power Query Arayüzünün Ötesine Geçmek
- Tablo. Bölünmüş
- Bill Szysz Dünyası
-
Formül Çözümleri
- Bir Dinamik Dizi Formülü
- Eski Okul Yardımcı Sütunları
- Formül Çözümleri
-
Yukarıdaki ve Son Videodan Tüm Fikirlerin Birleşimi
- En İyi Fikirlerin Birleşimi
