Enerģijas vaicājums: atkārtoti numurējiet ierakstu grupas no 1 līdz 5 - Excel padomi

Satura rādītājs

Piezīme

Šis ir viens no rakstu sērijas, kurā sīki aprakstīti Podcast 2316 izaicinājumam nosūtītie risinājumi.

Manā Power Query Challenge viens no soļiem bija vārda lauka ņemšana no katra 5. ieraksta un tā nokopēšana uz pieciem ierakstiem. Mans sākotnējais risinājums bija neveikls, rēķinoties ar to, ka nosaukuma garums būs garāks par 2 rakstzīmēm.

Vairāki cilvēki, tostarp MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers, izmantoja daudz labāku risinājumu, iesaistot Index kolonnu.

Paņemsim procesu, kur dati izskatās šādi:

Datu tabula

Pirmkārt, MF Vongs atzīmēja, ka jums nav vajadzīgi pirmie pieci ieraksti. Jūs varētu izmantot

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

Noņemiet augšējās rindas

Arī Excel MVP Oz du Soleil no Excel on Fire atbrīvojās no šiem pieciem, taču viņš to izdarīja, kad tās vēl bija kolonnas.

Pēc tam pievienojiet kolonnu, pievienojiet indeksa kolonnu no 0. Tādējādi tiek ģenerēta jauna kolonna ar 0 līdz NN.

Indeksa kolonna

Kad ir atlasīta jaunā sleja Indekss, dodieties uz cilni Pārveidot un cilnes Cilne grupā izvēlieties nolaižamo izvēlni Standarts. Esiet piesardzīgs: cilnē Pievienot kolonnu ir līdzīga nolaižamā izvēlne, taču cilnes Transformācija atlasīšana neļauj pievienot papildu kolonnu. Šajā nolaižamajā izvēlnē izvēlieties Modulo un pēc tam norādiet, ka atlikušo daļu vēlaties dalīt ar 5.

Modulo

Tad

Modulis

Tādējādi tiek ģenerēta virkne skaitļu no 0 līdz 4, kas atkārtojas atkārtoti.

Rezultāts

Darbinieku vārdu pārsūtīšanas darbības ir līdzīgas manam sākotnējam videoklipam.

Pievienojiet nosacītu kolonnu, kas pārnes vārdu vai vērtību Null un pēc tam aizpildiet uz leju. Vairāk veidu, kā aprēķināt šo kolonnu, ir atrodami sadaļā Power Query: citu nosacījumu izmantošana nosacītajās slejās.

Pievienojiet nosacīto kolonnu

Aizpildiet uz leju, lai aizpildītu vārdu no pirmās rindas līdz nākamajām piecām rindām.

Paldies MF Wong par viņa video. Noteikti ieslēdziet CC angļu valodas parakstiem.
https://www.youtube.com/watch?v=So1n7sLE_Mg

Pētera Bartolomeja video:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Maikls Karpfens arī saprata, ka nav nepieciešams dzēst kopsummas un vēlāk tās pievienot. Viņa M kods ir:

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"

Ņemiet vērā, ka Džošs Džonsons izmantoja arī kolonnu Index, bet kā vienu no pirmajiem soļiem un izmantoja to kā kārtojumu vienā no pēdējām darbībām.

Atgriezieties uz Podcast 2316 izaicinājuma galveno lapu.

Izlasiet nākamo šīs sērijas rakstu: Power Query: kreisās 2 rakstzīmes tiek izvilktas no kolonnas.