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

 

 

This article was written by Jeff Lenning

9 comments:

  1. E Elliott
    Reply

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

    1. Jeff Lenning Post author
      Reply

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

  2. Ferreira
    Reply

    Congratulations Jeff, excelent tip!

    1. Jeff Lenning Post author
      Reply

      Thanks πŸ™‚

  3. Monther
    Reply

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

    1. Jeff Lenning Post author
      Reply

      Thanks!

  4. marwa
    Reply

    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
    Reply

    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

    1. Jeff Lenning Post author
      Reply

      Awesome…Thanks πŸ™‚

Leave a Reply

Your email address will not be published. Required fields are marked *