Power Query: Antal grupper av poster som 1 till 5 upprepade gånger - Excel Tips

Innehållsförteckning

Notera

Detta är en av en serie artiklar som beskriver lösningar som skickats in för Podcast 2316-utmaningen.

I min Power Query Challenge var ett av stegen att ta namnfältet från var 5: e post och kopiera det till de fem posterna. Min ursprungliga lösning var klumpig och räknade med att namnet skulle vara längre än två tecken.

Flera personer, inklusive MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers använde en mycket bättre lösning med en indexkolumn.

Låt oss hämta processen där data ser ut så här:

Datatabell

Först noterade MF Wong att du inte behöver de första fem posterna. Du kan använda

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

Ta bort de övre raderna

Excel MVP Oz du Soleil från Excel on Fire blev också av med de fem, men han gjorde det när de fortfarande var kolumner.

Lägg sedan till kolumn, lägg till indexkolumn, från 0. Detta genererar en ny kolumn på 0 till och med NN.

Indexkolumn

Med den nya indexkolumnen markerad går du till fliken Transform och väljer rullgardinsmenyn Standard från gruppen Tab. Var försiktig: det finns en liknande rullgardinsmeny på fliken Lägg till kolumn, men att välja den på fliken Transform förhindrar att en extra kolumn läggs till. Välj Modulo från den här rullgardinsmenyn och ange sedan att du vill ha resten efter att ha delat med 5.

Modulo

Sedan

Modul

Detta genererar en serie siffror från 0 till 4 som upprepas om och om igen.

Resultat

Härifrån liknar stegen för att få medarbetarnamnen över min originalvideo.

Lägg till en villkorlig kolumn som antingen tar över namnet eller värdet Null och sedan Fill Down. Fler sätt att beräkna den här kolumnen finns i Power Query: Using Else If Clauses in Conditional Columns.

Lägg till villkorlig kolumn

Fyll ner för att fylla namnet från första raden till nästa fem rader.

Tack till MF Wong för hans video. Se till att aktivera CC för engelska bildtexter.
https://www.youtube.com/watch?v=So1n7sLE_Mg

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

Michael Karpfen insåg också att det inte finns något behov av att ta bort summan och lägga till dem senare. Hans M-kod är:

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"

Observera att Josh Johnson också använde en indexkolumn, men som ett av de allra första stegen och använde det som ett slags i ett av de sista stegen.

Gå tillbaka till huvudsidan för Podcast 2316-utmaningen.

Läs nästa artikel i denna serie: Power Query: Extrahera vänster 2 tecken från en kolumn.