Power Query: Nummer groepen records herhaaldelijk als 1 tot en met 5 - Excel-tips

Inhoudsopgave

Opmerking

Dit is een van een reeks artikelen over oplossingen die zijn ingezonden voor de Podcast 2316-uitdaging.

In mijn Power Query Challenge was een van de stappen om het naamveld van elke 5e record te kopiëren naar de vijf records. Mijn oorspronkelijke oplossing was onhandig, er rekening mee houdend dat de lengte van de naam langer zou zijn dan 2 tekens.

Verschillende mensen, waaronder MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil en Jamie Rogers, gebruikten een veel betere oplossing met een Index-kolom.

Laten we het proces oppakken waarbij de gegevens er als volgt uitzien:

Data tafel

Ten eerste merkte MF Wong op dat je de eerste vijf records niet nodig hebt. Je zou kunnen gebruiken

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

Verwijder de bovenste rijen

Excel MVP Oz du Soleil van Excel on Fire schrapte ook die vijf, maar hij deed het toen het nog kolommen waren.

Vervolgens Kolom toevoegen, Indexkolom toevoegen, Van 0. Dit genereert een nieuwe kolom van 0 tot en met NN.

Index kolom

Ga met de nieuwe Index-kolom geselecteerd naar het tabblad Transformeren en kies het vervolgkeuzemenu Standaard in de groep Tabblad Getallen. Let op: er is een vergelijkbare vervolgkeuzelijst op het tabblad Kolom toevoegen, maar als u die selecteert op het tabblad Transformeren, voorkomt u dat er een extra kolom wordt toegevoegd. Kies Modulo in deze vervolgkeuzelijst en geef vervolgens op dat u de rest wilt delen door 5.

Modulo

Vervolgens

Module

Dit genereert een reeks getallen van 0 tot 4 die keer op keer worden herhaald.

Resultaat

Vanaf hier zijn de stappen om de namen van de werknemers over te brengen vergelijkbaar met mijn originele video.

Voeg een voorwaardelijke kolom toe die de naam of de waarde Null overbrengt en vervolgens Invullen. Meer manieren om deze kolom te berekenen zijn te vinden in Power Query: Else If-clausules gebruiken in voorwaardelijke kolommen.

Voorwaardelijke kolom toevoegen

Vul Omlaag in om de naam van de eerste rij tot de volgende vijf rijen in te vullen.

Met dank aan MF Wong voor zijn video. Zorg ervoor dat u CC inschakelt voor Engelse ondertiteling.
https://www.youtube.com/watch?v=So1n7sLE_Mg

De video van Peter Bartholomew:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Michael Karpfen realiseerde zich ook dat het niet nodig is om de totalen te verwijderen en later weer toe te voegen. Zijn M-code is:

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 op dat Josh Johnson ook een Index-kolom gebruikte, maar als een van de allereerste stappen en dat als een soort in een van de laatste stappen.

Keer terug naar de hoofdpagina voor de Podcast 2316-uitdaging.

Lees het volgende artikel in deze serie: Power Query: twee linkertekens uit een kolom extraheren.