Excel MVP Menyerang Masalah Pembersihan Data dalam Kuasa Kuasa - Petua Excel

Isi kandungan

Catatan

Ini adalah salah satu rangkaian artikel yang memperincikan penyelesaian yang dihantar untuk cabaran Podcast 2316.

Excel MVP Oz Du Soleil dari saluran Excel on Fire di YouTube menyebutkan Brazilian Bull Rider Kaique Pachecho. Oz adalah orang pertama yang menyedari bahawa saya menggunakan kaedah perlahan untuk menambah empat suku.

Video Oz adalah:
https://www.youtube.com/watch?v=OluZlF44PNI

Kodnya adalah:

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"

Penyelesaian lain, ini dari Excel MVP John MacDougall.

  • John adalah yang pertama mengatakan bahawa dengan menghapus dua langkah tambahan Power Query yang ditambahkan, anda menghilangkan akhiran ganjil pada tajuk Q1 Q2 Q3 Q4 pendua.
  • John menggunakan lajur Indeks lebih awal yang akan digunakan pada akhir untuk menyusun. Tetapi - John menggabungkan kolum indeksnya selepas perihalan kategori. Dia menggunakan watak paip menegak | supaya dia dapat memecahkan data kemudian.
  • John menaip lajur bersyaratnya sebagai lajur Custom dan bukannya menggunakan antara muka Lajur Bersyarat.
Lajur bersyarat sebagai lajur tersuai

Tonton video John di sini:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Excel MVP Ken Puls, pengarang bersama M adalah untuk (Data) buku Monyet yang dihantar dalam tiga penyelesaian. Lajur bersyaratnya mungkin paling pendek.

Tetapi penyelesaian pilihan Ken mengabaikan persoalan asalnya. Daripada membuat jadual di Power Query, dia membuat set data pivotable di Power Query dan kemudian menyelesaikan dengan jadual pangsi.

Pratonton terakhir Ken dalam Kueri Kuasa kelihatan seperti ini:

Kumpulan data berpusing

Inilah kod Ken:

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"

Setelah membuat pertanyaan ini sebagai sambungan sahaja, dia kemudian menggunakan jadual pangsi untuk membuat laporan akhir.

Laporan akhir dengan Jadual Pangsi

Penyelesaian dari MVP lain:

  • Kod Wyn Hopkins ada di sini: Pertanyaan Kuasa: Berurusan dengan Pelbagai Tajuk yang Sama.
  • Kod Mike Girvin ada di sini: Pertanyaan Kuasa: Mengeluarkan Karakter 2 Kiri Dari Lajur.
  • Penyelesaian formula Roger Govier ada di sini: Penyelesaian Formula.

Kembali ke halaman utama untuk cabaran Podcast 2316.

Baca artikel seterusnya dalam siri ini: Kuasa Kuasa: Di Luar Antara Muka Pengguna: Jadual. Split dan Banyak Lagi.

Artikel menarik...