Unpivot

Depending on your source system, you might have to change the structure of your data in order to use it in Power BI. You can use Power Query Editor to unpivot your data in case it's arranged in columns instead of rows.

In this article

  1. Select columns to unpivot
  2. Change data to Date type
  3. Rename necessary columns


 

   

In general, you want to have  long (many rows) and  narrow (little columns) tables. 

In case your data is structured in a way that you have the same dimension (in this example time/months) in separate columns, looking something like this:

 

Then you need to use a function called unpivot to turn it into this where all months are in one column:

  

Let's see how you can use the unpivot function in Power Query Editor

  

Select the header of the column(s) which don’t need to be changed. In this case, the column Account contains all values from the same dimension (Accounts), which is correct and can stay the same. We want to unpivot all other columns so the option that needs to be selected is Unpivot Other Columns.

   

Selecting all other columns and use the function Unpivot Only Selected Columns would end up with the same result but wouldn’t take newly added columns (months) into account. This is why you should use Unpivot Other Columns.

The result looks like the picture below, where the periods/months (Attribute) and values have been moved into separate columns. There are only a few cleanup steps that need to be done.

  

The data types of the newly created columns need to be changed to Date and Whole Number.

  

    

The Attribute column should be renamed to Time.

  

Now you are ready to use this data in Power BI. Having tables like this makes the setup of a proper report much easier as you can create proper measures. On top of that, wide tables might slow down the report but long tables are usually not a problem. 

  

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us