Catatan
Ini adalah salah satu dari rangkaian artikel yang merinci solusi yang dikirim untuk tantangan Podcast 2316.
Salah satu masalah dengan solusi saya adalah bahwa urutan terakhir kategori tidak selalu sesuai dengan urutan kolom asli. Saya menyadari hal ini di akhir video saya, dan karena itu tidak terlalu penting, saya tidak mengkhawatirkannya.
Namun, Josh Johnson mengirimkan solusi yang menanganinya. Ketika Josh mengatakan dia menggunakan kolom Indeks, saya berasumsi itu seperti Indeks dan Modulo di Power Query: Jumlah Kelompok Catatan sebagai 1 sampai 5 berulang kali. Tapi penggunaan Josh sama sekali berbeda.
Catatan: MVP Excel John MacDougall juga menggunakan metode ini, tetapi dia menggabungkan kolom indeks ke akhir deskripsi kategori. Lihat video John di sini: https://www.youtube.com/watch?v=Dqmb6SEJDXI dan baca lebih lanjut tentang kodenya di sini: MVP Excel Menyerang Masalah Pembersihan Data di Power Query.
Di awal proses, ketika Josh masih memiliki enam catatan, dia menambahkan indeks mulai dari 1. Josh mengklik di bilah rumus dan mengganti nama kolom Indeks menjadi Kategori.
Kolom Kategori adalah kolom terakhir yang baru. Dia menggunakan Move, to Beginning untuk memindahkannya menjadi yang pertama:
Setelah ini, banyak langkah lain terjadi. Mereka adalah langkah-langkah yang inovatif tetapi sebagian besar telah dibahas di artikel lain sejauh ini. Setelah banyak langkah seperti itu, saya mulai berpikir bahwa Kategori nomor 1 sampai 6 hanyalah sebuah kesalahan. Saya pikir mungkin Josh akan menghapusnya tanpa menggunakannya.
Josh Unpivots, lalu kolom kondisional, lalu isi, lalu putar, tambahkan total. Dia sepertinya tidak pernah menggunakan kolom Kategori itu. Setelah banyak langkah, dia ada di sini:
Tapi kemudian di langkah terakhir, Josh mengurutkan data berdasarkan Nama Karyawan lalu Kategori!
Pada titik ini, dia dapat menghapus kolom Kategori. Perbedaan terakhir: PTO muncul sebelum Proyek A, seperti yang ada di kolom aslinya. Ini sentuhan yang bagus.
Saya juga akan menunjukkan bahwa Josh mengirimkan videonya melalui langkah-langkah ini. Kudos to Josh karena menggunakan pintasan keyboard di dalam Power Query!
Ini kode 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 tantangan Podcast 2316.
Baca artikel berikutnya dalam seri ini: MVP Excel Menyerang Masalah Pembersihan Data di Power Query.
