Catatan
Ini adalah salah satu rangkaian artikel yang memperincikan penyelesaian yang dihantar untuk cabaran Podcast 2316.
Salah satu masalah dengan penyelesaian saya ialah urutan akhir kategori tidak semestinya sesuai dengan turutan lajur yang asal. Saya menyedari ini di hujung video saya, dan kerana ia tidak begitu penting, saya tidak bimbang.
Walau bagaimanapun, Josh Johnson menghantar penyelesaian yang mengatasinya. Ketika Josh mengatakan dia menggunakan lajur Indeks, saya menganggapnya seperti Indeks dan Modulo dalam Kueri Kuasa: Nombor Kumpulan Rekod sebagai 1 hingga 5 berulang kali. Tetapi penggunaan Josh sama sekali berbeza.
Catatan: Excel MVP John MacDougall juga menggunakan kaedah ini, tetapi dia menggabungkan lajur indeks hingga akhir keterangan kategori. Lihat video John di sini: https://www.youtube.com/watch?v=Dqmb6SEJDXI dan baca lebih lanjut mengenai kodnya di sini: MVP Excel Menyerang Masalah Pembersihan Data dalam Kuasa Kuasa.
Pada awal proses, ketika Josh masih memiliki enam catatan, dia menambahkan indeks mulai 1. Josh mengklik pada bar formula dan menamakan semula lajur Indeks sebagai Kategori.
Lajur Kategori adalah lajur terakhir yang baru. Dia menggunakan Move, to Beginning untuk memindahkannya menjadi yang pertama:
Selepas ini, banyak langkah lain berlaku. Ini adalah langkah-langkah yang inovatif tetapi kebanyakannya telah dibahas dalam artikel lain setakat ini. Setelah melakukan banyak langkah seperti itu, saya mula berfikir bahawa Kategori nombor 1 hingga 6 hanyalah satu kesalahan. Saya fikir mungkin Josh akan menghapusnya tanpa menggunakannya.
Josh Unpivots, kemudian kolum bersyarat, kemudian isi, kemudian pivot, menambahkan jumlahnya. Dia nampaknya tidak menggunakan ruangan Kategori itu. Selepas banyak langkah, dia ada di sini:
Tetapi pada langkah terakhir, Josh menyusun data mengikut Nama Pekerja dan kemudian Kategori!
Pada ketika ini, dia dapat menghapus lajur Kategori. Perbezaan terakhir: PTO hadir sebelum Projek A, seperti yang terdapat di ruangan asal. Ia adalah sentuhan yang bagus.
Saya juga akan menunjukkan bahawa Josh menghantar video dia melalui langkah-langkah ini. Kudos kepada Josh kerana menggunakan pintasan papan kekunci di dalam Power Query!
Inilah kod Josh:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Kembali ke halaman utama untuk cabaran Podcast 2316.
Baca artikel seterusnya dalam siri ini: MVP Excel Menyerang Masalah Pembersihan Data dalam Kuasa Kuasa.
