Power Query: Jumlahkan Grup Rekaman sebagai 1 hingga 5 secara berulang - Tips Excel

Daftar Isi

Catatan

Ini adalah salah satu dari rangkaian artikel yang merinci solusi yang dikirim untuk tantangan Podcast 2316.

Dalam Power Query Challenge saya, salah satu langkahnya adalah mengambil bidang nama dari setiap catatan ke-5 dan menyalinnya ke lima catatan. Solusi asli saya kikuk, mengandalkan fakta bahwa panjang nama akan lebih dari 2 karakter.

Beberapa orang, termasuk MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers menggunakan solusi yang jauh lebih baik yang melibatkan kolom Indeks.

Mari kita ambil proses dimana datanya terlihat seperti ini:

Tabel data

Pertama, MF Wong mencatat bahwa Anda tidak memerlukan lima rekaman pertama. Anda bisa menggunakan

Home, Remove Rows, Remove Top Rows… , 5 Rows.

Hapus baris atas

Excel MVP Oz du Soleil dari Excel on Fire juga menyingkirkan lima itu, tetapi dia melakukannya ketika mereka masih kolom.

Kemudian, Tambah Kolom, Tambah Kolom Indeks, Dari 0. Ini menghasilkan kolom baru dari 0 sampai NN.

Kolom indeks

Dengan kolom Indeks baru dipilih, buka tab Transformasi dan pilih menu drop-down Standar dari grup Tab Angka. Hati-hati: ada drop-down serupa di tab Add Column, tetapi memilih salah satu di tab Transform mencegah penambahan kolom tambahan. Pilih Modulo dari drop-down ini dan kemudian tentukan bahwa Anda menginginkan sisanya setelah membaginya dengan 5.

Modulo

Kemudian

Modul

Ini menghasilkan serangkaian angka dari 0 sampai 4 yang berulang-ulang.

Hasil

Dari sini, langkah-langkah untuk memunculkan nama karyawan serupa dengan video asli saya.

Tambahkan kolom bersyarat yang membawa nama atau nilai Null lalu Isi ke Bawah. Lebih banyak cara untuk menghitung kolom ini ditemukan di Power Query: Menggunakan Else If Klausul di Kolom Bersyarat.

Tambahkan kolom bersyarat

Fill Down untuk mengisi nama dari baris pertama hingga lima baris berikutnya.

Terima kasih kepada MF Wong untuk videonya. Pastikan untuk mengaktifkan CC untuk teks bahasa Inggris.
https://www.youtube.com/watch?v=So1n7sLE_Mg

Video Peter Bartholomew:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Michael Karpfen juga menyadari bahwa tidak perlu menghapus total dan menambahkannya kembali nanti. Kode M-nya adalah:

let Quelle = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Höher gestufte Header" = Table.PromoteHeaders(Quelle, (PromoteAllScalars=true)), #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",(("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))), #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", ("Category Description"), "Attribut", "Wert"), #"Hinzugefügter Index" = Table.AddIndexColumn(#"Entpivotierte andere Spalten", "Index", 1, 1), #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "Benutzerdefiniert", each Number.Mod((Index)-1,5)), #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Benutzerdefiniert.1", each if (Benutzerdefiniert)=0 then (Attribut) else null), #"Nach unten gefüllt" = Table.FillDown(#"Hinzugefügte benutzerdefinierte Spalte1",("Benutzerdefiniert.1")), #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Nach unten gefüllt",("Benutzerdefiniert.1", "Attribut", "Category Description", "Wert", "Index", "Benutzerdefiniert")), #"Geänderter Typ1" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",(("Benutzerdefiniert", type text))), #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ1", "Benutzerdefiniert.2", each if (Benutzerdefiniert) = "0" then "TOTAL" else "Q"&(Benutzerdefiniert)), #"Neu angeordnete Spalten1" = Table.ReorderColumns(#"Hinzugefügte benutzerdefinierte Spalte2",("Attribut", "Category Description", "Benutzerdefiniert.1", "Wert", "Index", "Benutzerdefiniert", "Benutzerdefiniert.2")), #"Entfernte Spalten" = Table.RemoveColumns(#"Neu angeordnete Spalten1",("Attribut", "Index", "Benutzerdefiniert")), #"Pivotierte Spalte" = Table.Pivot(#"Entfernte Spalten", List.Distinct(#"Entfernte Spalten"(Benutzerdefiniert.2)), "Benutzerdefiniert.2", "Wert", List.Sum), #"Neu angeordnete Spalten2" = Table.ReorderColumns(#"Pivotierte Spalte",("Benutzerdefiniert.1", "Category Description", "Q1", "Q2", "Q3", "Q4", "TOTAL")), #"Sortierte Zeilen" = Table.Sort(#"Neu angeordnete Spalten2",(("Benutzerdefiniert.1", Order.Ascending))), #"Umbenannte Spalten" = Table.RenameColumns(#"Sortierte Zeilen",(("Benutzerdefiniert.1", "Employee Name"))) in #"Umbenannte Spalten"

Perhatikan bahwa Josh Johnson juga menggunakan kolom Indeks, tetapi sebagai salah satu langkah pertama dan menggunakannya sebagai penyortiran di salah satu langkah terakhir.

Kembali ke halaman utama untuk tantangan Podcast 2316.

Baca artikel berikutnya dalam seri ini: Power Query: Mengekstrak 2 Karakter Kiri Dari Kolom.