Using Power Query to convert Repeating Rows to Columns

Problem

This article was inspired by a problem I had recently and when searching I found this forum post that outlined the solution. This is a problem if you have CSV/TXT files in this repeated row format or have a set of files that you need to merge.

For my specific problem, I had a set of Excel invoices in a folder, and only needed to get a few values from each file. Each file had the same structure, so I was able to merge them together and filter out the columns to get a dataset like the table on the left. My end goal was to transform the dataset to the table on the right.

This article assumes limited familiarity with Power Query.

Transform Repeating Rows to a Column format table

Solution

The screenshots below are from Excel Power Query, but the same steps can be used for Power BI.

Add an index column starting from 0.

Add Index column from zero
Add a new custom column to calculate the Row Id. Make sure you replace "5" with the number of data fields that you have.

Add Custom Column for Row ID using Number.IntegerDivide function
Remove the Index column.

Remove the Index Column
Pivot on the "Row Id" using "Column 2" as the Values Column. Make sure you select "Don’t Aggregate".

Pivot the Row ID and select don’t aggregate

Now you will have a dataset close to what you need.

Dataset almost transformed
To finish off, you will need to: Transpose the table; promote the headers; reorder and rename the columns; and set the appropriate data types.

The full Power Query code is below:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Row ID", each Number.IntegerDivide([Index], 5)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Row ID", type text}}, "en-NZ"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Row ID", type text}}, "en-NZ")[#"Row ID"]), "Row ID", "Column2"),
#"Transposed Table" = Table.Transpose(#"Pivoted Column"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"

Hopefully this will help someone. Happy coding and Power Querying!