Power Query: Antall grupper av poster som 1 til 5 gjentatte ganger - Excel Tips

Innholdsfortegnelse

Merk

Dette er en av en serie artikler som beskriver løsninger som er sendt inn for Podcast 2316-utfordringen.

I Power Query Challenge var en av trinnene å ta navnefeltet fra hver 5. plate og kopiere det ned til de fem postene. Den opprinnelige løsningen min var klumpete, og regnet med at navnet på lengden ville være lengre enn 2 tegn.

Flere personer, inkludert MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers, brukte en mye bedre løsning med en indeks-kolonne.

La oss hente prosessen der dataene ser slik ut:

Data bord

Først bemerket MF Wong at du ikke trenger de fem første postene. Du kan bruke

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

Fjern de øverste radene

Excel MVP Oz du Soleil fra Excel on Fire kvittet seg også med de fem, men han gjorde det da de fremdeles var kolonner.

Deretter legger du til kolonne, legger til indekskolonne, fra 0. Dette genererer en ny kolonne på 0 til og med NN.

Indeks kolonne

Når den nye indekskolonnen er valgt, går du til Transform-fanen og velger Standard-rullegardinmenyen fra Number Tab-gruppen. Vær forsiktig: det er en lignende rullegardin på fanen Legg til kolonne, men å velge den på Transform-fanen forhindrer å legge til en ekstra kolonne. Velg Modulo fra denne rullegardinmenyen, og spesifiser deretter at du vil ha resten etter å ha delt med 5.

Modulo

Deretter

Modul

Dette genererer en rekke tall fra 0 til 4 gjentatt om og om igjen.

Resultat

Herfra ligner trinnene for å bringe ansattens navn over den originale videoen min.

Legg til en betinget kolonne som enten fører over navnet eller verdien Null og deretter Fill Down. Flere måter å beregne denne kolonnen på, finner du i Power Query: Using Else If Clauses in Conditional Columns.

Legg til betinget kolonne

Fyll ned for å fylle navnet fra første rad til de neste fem radene.

Takk til MF Wong for videoen. Sørg for å slå på CC for engelsk teksting.
https://www.youtube.com/watch?v=So1n7sLE_Mg

Peter Bartholomews video:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Michael Karpfen innså også at det ikke er behov for å slette totalene og legge dem tilbake senere. Hans M-kode er:

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"

Merk at Josh Johnson også brukte en indekskolonne, men som en av de aller første trinnene og brukte den som en slags i et av de siste trinnene.

Gå tilbake til hovedsiden for Podcast 2316-utfordringen.

Les neste artikkel i denne serien: Power Query: Extracting Left 2 Characters From a Column.