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.

Objective

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.

Details

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.

Query editor by Jeff Lenning

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.

Transposed data by Jeff Lenning

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.

Results table by Jeff Lenning

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!

Additional Resources

 

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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?

Our training programs start at $29 and will help you learn Excel quickly.

9 Comments

  1. E Elliott on April 27, 2017 at 6:17 am

    Very useful posts on your log. Enjoy learning new tips and tricks. Appreciate you very much

    • Jeff Lenning on April 27, 2017 at 6:18 am

      Thanks! I learn new stuff about Excel all the time too, it is so fun ๐Ÿ™‚

  2. Ferreira on April 27, 2017 at 2:44 pm

    Congratulations Jeff, excelent tip!

    • Jeff Lenning on April 27, 2017 at 2:45 pm

      Thanks ๐Ÿ™‚

  3. Monther on April 27, 2017 at 10:18 pm

    Great tip, Jeff. I am learning great things about Excel by going through your posts.

    • Jeff Lenning on April 28, 2017 at 3:25 am

      Thanks!

  4. marwa on April 30, 2017 at 5:06 am

    Great work, Jeff. you encourage me to learn more tricks in excel; as i was always afraid to take complicated tasks in excel;
    Now everything is easy with you, Many thanks, appreciated ๐Ÿ™‚

  5. marwa on April 30, 2017 at 5:07 am

    Great work, Jeff. you encourage me to learn more tricks in excel; as i was always afraid to take complicated tasks in excel;
    Now everything is easy with you, Many thanks, appreciated ๐Ÿ™‚
    Love your blog

    • Jeff Lenning on April 30, 2017 at 6:00 am

      Awesome…Thanks ๐Ÿ™‚

Leave a Comment