Often we receive data in a format that is similar to a PivotTable, but that is not conducive to using that data to create a PivotTable to analyze that data. In order to convert the data to a tabular format to create our PivotTable, in this tutorial we will use Power Query to accomplish this.
Power Query was introduced by Microsoft in the last couple of versions. Here are a couple links you might find useful to learn about and download this feature:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a data range that is in a PivotTable style format rather than a tabular format. Notice the Agents and months are in the rows area while the sales for each of the three years are in the columns.
This is not conducive to utilizing it for creating PivotTables. So what we need to do is “unpivot” the data to get it in the correct format, and we’ll use Power Query to do that.
On the Power Query tab, you can see that I can import data from a variety or sources. One of those is a table:
I can either convert the data into a Table using the keyboard shortcut Ctrl + T, or I can let Power Query do it by just clicking anywhere in the data range and choosing “From Table”. Power Query will identify the range, ask me if I have headers, then when I click OK, will open up the Power Query Editor:
Here you can see that Power Query is set up similar to Excel with a Ribbon, rows and columns.
My next step is to decide which columns I want to “unpivot”. In our case, we want to unpivot the three columns that have the sales values by year. I can either choose those three to unpivot, or I can select the first to columns and choose to unpivot the other columns. To do this, I cal select the columns I want using my Ctrl key, then go to the Transform tab of the Ribbon and choose the “Unpivot Columns” command. Here I can select either to unpivot the columns I’ve chosen or those that are not chosen. Since I selected the ones that I don’t want to unpivot, I’ll choose to unpivot the other columns:
And you can see that Power Query has converted our data so that each month for each salesperson has three rows of sales data, one for each year:
Now I can double click on the header for those last two columns and change them to “Year” and “Sales” respectively. Once I’ve done that, I will go to the Home tab of Power Query and choose the “Close & Load” command:
Excel has now inserted a new worksheet with a Table containing the unpivoted data that we just created in Power Query:
Now that my data is in the proper tabular format, I can create the PivotTable that I wanted:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!