Use a Get & Transform Query to Transpose Values
Recently, I’ve been on this kick of using Get & Transform queries to accomplish tasks that we previously performed with other methods. It is clear that the Get & Transform commands provide new ways to solve old problem. In this post, I’ll demonstrate how we can use a Get & Transform query to transpose values.
Before we get too far, let’s just be sure we are on the same page. Sometimes, data comes to us in one format, but, we need it in another. For example, the data below displays regions in rows and items in columns.
But, for our report, we need regions in columns and items in rows. We need to transpose the orientation, as shown below.
In a previous post, I demonstrated three ways to accomplish this task, including Paste Special, using formulas, and using a PivotTable. In this post, we’ll accomplish it with a Get & Transform query.
If you like to learn the steps by watching a video, I’ve included the video below. If you’d prefer to learn the steps by reading a narrative, I’ve included the details below the video.
This transformation is simple with a Get & Transform query, and the best part is that it is easy to refresh in subsequent periods. We’ll accomplish this task with the following steps.
- Load the source
- Perform the transformation
- Return the data to Excel
Let’s jump right in.
Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.
Load the source
To load the source, we select the entire range of data, and select Data > From Table. If the data is already stored in a table or a name, then, the Query Editor will open immediately. If not, you’ll first see the Create Table dialog asking you to confirm that you want to convert the ordinary range into a table (which you do).
The query editor opens, and provides a preview as shown below.
With the data source loaded into the editor, it is time for the next step.
Perform the transformation
This step is really complex 🙁
Just kidding, it is really easy 🙂 We just need to click three buttons.
First button: Since the values we need were actually used as column headers, we need to click the Use Headers As First Row command (which is located on the Home > Use First Row As Headers drop down, and the Transpose > Use First Row As Headers drop down). This will “demote” the header labels and push them down into the data as values.
Second button: Now, we just click the Transform > Transpose ribbon command. And just like that, the data is transposed.
Third button: Now, we just need to tell Excel to use the first row of values as the column labels. We basically need to “promote” the values. So, we click the Use First Row As Headers button. The updated query, with items in rows and regions in columns, is shown below.
Now, we just need to get the data back to Excel.
Return the data to Excel
To return the query results to Excel, we just click the Home > Close & Load command. And bam…our transposed data is back in Excel.
But, Jeff…hang on man…this is way more complex than doing a copy/paste special transpose. Well, if this were a one-time task, I am right there with you. Let’s just manually copy/paste, get er done, and move on with our life. But, if we are doing this on a recurring basis, daily, weekly, monthly…then…the benefit of this approach becomes clear. Next period, we simply right-click the results table, and select Refresh. The results table is immediately update to include any changes to the original table.
If you have any other fun Get & Transform query tricks, or have used a G&T query as an alternative to other methods, please share by posting a comment below…thanks!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.