„Power Query“: pakartotinai skaičiuokite įrašų grupes nuo 1 iki 5 - „Excel“ patarimai

Turinys

Pastaba

Tai vienas iš straipsnių serijos, kuriame pateikiami išsamūs sprendimai, atsiųsti iššūkiui „Podcast 2316“.

Mano „Power Query Challenge“ metu vienas iš žingsnių buvo paimti vardo lauką iš kiekvieno 5 įrašo ir nukopijuoti jį į penkis įrašus. Mano originalus sprendimas buvo nepatogus, skaičiuojant tai, kad vardo ilgis bus ilgesnis nei 2 simboliai.

Keli žmonės, įskaitant MF Wongą, Michaelą Karpfeną, Peterį Bartholomewą, Chrisą McNeilą, Jamie Rogersą, naudojo žymiai geresnį sprendimą, įtraukiant „Index“ skiltį.

Paimkime procesą, kuriame duomenys atrodo taip:

Duomenų lentelė

Pirma, MF Wongas pažymėjo, kad nereikia pirmųjų penkių įrašų. Galėtumėte naudoti

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

Pašalinkite viršutines eilutes

„Excel on Fire“ taip pat atsikratė „Excel MVP Oz Oz Soleil“ iš „Excel on Fire“, tačiau jis tai padarė, kai jie dar buvo kolonos.

Tada pridėkite stulpelį, pridėkite indekso stulpelį, nuo 0. Tai sukuria naują stulpelį nuo 0 iki NN.

Rodyklės stulpelis

Pasirinkę naują indekso stulpelį, eikite į skirtuką „Transformuoti“ ir grupės „Skirtukas skirtukas“ pasirinkite išskleidžiamąjį meniu Standartas. Būkite atsargūs: skirtuke Pridėti stulpelį yra panašus išskleidžiamasis meniu, tačiau pasirinkus skirtuką Transformuoti, negalima pridėti papildomo stulpelio. Šiame išskleidžiamajame meniu pasirinkite „Modulo“, tada, padaliję iš 5, nurodykite, kad norite likutį.

Modulo

Tada

Modulis

Tai sukuria skaičių eilę nuo 0 iki 4, kartojamų vėl ir vėl.

Rezultatas

Darbuotojų vardų perkėlimo veiksmai yra panašūs į mano originalų vaizdo įrašą.

Pridėkite sąlyginį stulpelį, kuris perkelia pavadinimą arba reikšmę „Nulis“, tada užpildykite žemyn. Daugiau būdų apskaičiuoti šį stulpelį rasite „Power Query“: sąlyginių stulpelių kitų sąlygų naudojimas.

Pridėti sąlyginį stulpelį

Užpildykite žemyn, kad užpildytumėte vardą nuo pirmos eilės iki kitų penkių eilučių.

Ačiū MF Wong už jo vaizdo įrašą. Įsitikinkite, kad įjungėte CC, jei norite naudoti angliškus antraštes.
https://www.youtube.com/watch?v=So1n7sLE_Mg

Peterio Bartholomewo vaizdo įrašas:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Michaelas Karpfenas taip pat suprato, kad nereikia ištrinti sumų ir jas vėliau pridėti. Jo M kodas yra:

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"

Atkreipkite dėmesį, kad Joshas Johnsonas taip pat naudojo indekso stulpelį, bet kaip vieną iš pirmųjų žingsnių ir naudojo tai kaip rūšiavimą viename iš paskutinių žingsnių.

Grįžkite į pagrindinį „Podcast 2316“ iššūkio puslapį.

Perskaitykite kitą šios serijos straipsnį: „Power Query“: iš kairės 2 simbolių išskyrimas iš stulpelio.