Convert Row Data Into Columns with Modulo

This step by step guide will walk through a method of converting row data into columns with a Modulo column. A relatively common issue when building Power BI models is when the input data is formatted in rows versus columns. This is because Power BI works best with data formatted into columns since the software uses database logic and formatting. There is a way to accomplish this by using a Modulo column in Power Query and pivoting the data in a specific way that allows us to convert the data into a usable format. This guide will hopefully provide some ideas for your specific use case and reduce the need for any manual formatting of your data.

Example input sales data formatted in rows:

Power BI Input data formatted into rows

Desired data formatted into columns:

Power BI input data formatted into columns

Import Data into Power BI

The first step is to import the data into Power BI, for this example we are using a basic excel data connection.

Power BI Importing data

Transpose and Unpivot the Data

Once the data is loaded into your model navigate over to the Power Query Editor and it should look similar to the example data we are using.

Power BI, Power Query data imported as rows

Next select the Transform tab and select Transpose.

Power BI, Power Query Transpose data

The data should now be in the following format.

Power BI, Power Query Data

Navigate over to the Home tab and select Use First Row as Headers.

Power BI, Power Query Use First Row as Headers

Now we need to Unpivot each of the Product columns and move them back to their corresponding locations within the data set. In our example we highlight Product 1 and under the Transform Tab select Unpivot Columns.

Power BI, Power Query Unpivot Data
Power BI, Power Query Transform Unpivot Columns

The Unpivoted column will appear to the far-right side of the data set so we need to move it back to the beginning, in our example we move the Product 1 column and Month column in front of the North American Sales column for Product 1.

Power BI, Power Query Transform Data
Power BI, Power Query Transform Data

Repeat this step for each Product category and at the end of this process the Applied Steps should look similar to this.

Power BI, Power Query Applied Steps

Merge the Columns

Next navigate to the Transform tab, select all of the columns and click Merge Columns and choose Semicolon from the Separator drop-down menu.

Power BI, Power Query Merge Columns

The result will look similar to this.

Power BI, Power Merged Columns

Next under the Add Column tab select Custom Column and use the Text.Split formula:

= Text.Split([Merged],”;”)

Power BI, Power Query insert custom column

On the new Custom column, click the top right arrow symbol and select Expand to New Rows.

Power BI, Power Query expand to new rows
Power BI, Power Query Transform Data

Insert Modulo Column

Under the Add Column tab insert Index Column From 0. Also under the Add Column tab under the From Number section select the Standard drop-down and select Modulo.

Power BI, Power Query insert Modulo column

This where you would enter the number of rows before they repeat again, the Modulo column starts at zero and calculates a number for each row up to the number entered and then in repeats again until the end of the data set. In this example we have six columns before Product 2  so the Modulo column will enter 0 to 5 for the Product 1 rows then 0 to 5 for the product 2 row and so on. It is useful at this point to note what Modulo number corresponds to each row. In this example 0 = Product 1 = Month 2 = North America Sales 3 = South America Sales 4 = Europe Sales 5 = Asia Sales.

Power BI, Power Query insert Modulo column
Power BI, Power Query insert Modulo column

Pivot the Modulo Column

Now we will highlight the Modulo column we inserted and under the Transform tab select Pivot Column. In the Values Column drop down select the Custom column we added and expand the Advanced Options section and select Don’t Aggregate.

Power BI, Power Query Pivot Modulo Column

Once we have the data pivoted, we simply fill down for each column and once this is complete right click on the far-right column and select Remove Duplicates. You can also delete the Merged and Index columns as well as update the column labels. The row input data has now been formatted into columns, which is a useable format for completing additional calculations in your Power BI model. 

Power BI, Power Query Fill Down
Power BI, Power Query data formatted into columns

It is always a challenge to get input data into the desired format, this guide went through the steps to convert a relatively common row data format into columns. One limitation of this method is there cannot be any duplicate values in the dataset. A workaround is to include decimals in the input figures. Hopefully this guide provides some ideas for specific use cases, many different variations of this method can assist with data formatting, enabling greater functionality in your Power BI models.