Power Query: Number Groups of Records as 1 through 5 repeatedly

February 25, 2020 - by Bill Jelen

In my Power Query Challenge, one of the steps was to take the name field from every 5th record and copy it down to the five records. My original solution was clunky, counting on the fact that the length of the name would be longer than 2 characters.

Several people, including MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers used a much better solution involving an Index column.

Let's pick up the process where the data looks like this:

First, MF Wong noted that you don't need the first five records. You could use

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

Excel MVP Oz du Soleil from Excel on Fire also got rid of those five, but he did it when they were still columns.

Then, Add Column, Add Index Column, From 0. This generates a new column of 0 through NN.

With the new Index column selected, go to the Transform tab and choose the Standard drop-down menu from the Number Tab group. Be careful: there is a similar drop-down on the Add Column tab, but selecting the one on the Transform tab prevents adding an extra column. Choose Modulo from this drop-down and then specify that you want the remainder after dividing by 5.

Then

This generates a series of numbers from 0 to 4 repeated over and over.

From here, the steps to bring the employee names over are similar to my original video.

Add a conditional column that either brings over the name or the value Null and then Fill Down. More ways to calculate this column are found in Power Query: Using Else If Clauses in Conditional Columns.

Fill Down to fill the name from the first row to the next five rows.

Thanks to MF Wong for his video. Make sure to turn on CC for English captions.
https://www.youtube.com/watch?v=So1n7sLE_Mg

Peter Bartholomew's video:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Michael Karpfen also realized that there is no need to delete the totals and add them back later. His 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"

Note that Josh Johnson also used an Index column, but as one of the very first steps and used that as a sort in one of the final steps.

Return to the main page for the Podcast 2316 challenge.

Read the next article in this series: Power Query: Extracting Left 2 Characters From a Column.