Excel MVP'leri Power Query'de Veri Temizleme Sorununa Saldırıyor - Excel İpuçları

İçindekiler

Not

Bu, Podcast 2316 yarışması için gönderilen çözümleri ayrıntılarıyla anlatan bir dizi makaleden biridir.

YouTube'daki Excel on Fire kanalından Excel MVP Oz Du Soleil, Brezilyalı Bull Rider Kaique Pachecho'dan bahsetti. Oz, dört çeyreği eklemek için yavaş gittiğimi ilk fark eden kişiydi.

Oz'un videosu:
https://www.youtube.com/watch?v=OluZlF44PNI

Onun kodu:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

Başka bir çözüm, bu Excel MVP John MacDougall'dan.

  • John, Power Query'nin eklediği fazladan iki adımı silerek, yinelenen Q1 Q2 Q3 Q4 başlıklarındaki garip son ekleri ortadan kaldırdığınızı ilk söyleyen kişiydi.
  • John, en sonunda sıralama için kullanılacak olan bir Dizin sütunu kullandı. Ancak - John, dizin sütununu kategori açıklamasından sonra birleştirdi. Dikey boru karakteri kullandı | böylece verileri daha sonra kırabilirdi.
  • John, Koşullu Sütun arabirimini kullanmak yerine koşullu sütununu Özel sütun olarak yazdı.
Özel bir sütun olarak koşullu sütun

John'un videosunu buradan izleyin:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

M'nin ortak yazarı Excel MVP Ken Puls, üç çözümde gönderilen (Veri) Maymun kitabı içindir. Koşullu yazısı muhtemelen en kısa olanıdır.

Ancak Ken'in tercih ettiği çözüm asıl soruyu göz ardı ediyor. Power Query'de tablo oluşturmak yerine, Power Query'de pivotlanabilir bir veri kümesi oluşturur ve ardından bir pivot tabloyla bitirir.

Ken'in Power Query'deki son önizlemesi şuna benzer:

Özetlenebilir veri kümesi

İşte Ken'in 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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "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))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Bu sorguyu yalnızca bağlantı olarak oluşturduktan sonra, son raporu oluşturmak için bir pivot tablo kullanır.

Özet Tablo ile nihai rapor

Diğer MVP'lerden çözümler:

  • Wyn Hopkins kodu burada: Power Query: Çoklu Özdeş Başlıklar ile Başa Çıkma.
  • Mike Girvin'in kodu burada: Power Query: Bir Sütundan Soldaki 2 Karakteri Çıkarma.
  • Roger Govier'in formül çözümü burada: Formula Çözümleri.

Podcast 2316 yarışması için ana sayfaya dönün.

Bu dizideki sonraki makaleyi okuyun: Power Query: Kullanıcı Arayüzünün Ötesinde: Table.Split ve Daha Fazlası.

Ilginç makaleler...