Get & Transform: An Alternative to Manually Flattening Data

Since many of Excel’s features are designed to work with data stored in a flat, tabular format, we sometimes need to flatten data that is received in other formats. In a previous post, we discussed a manual way to flatten data. In this post, we’ll use a Get & Transform Query as an alternative to that manual process.

Objective

The data we receive is not flat, as shown below.

Data prep by Jeff Lenning

Since the Name and Account labels are not repeated for each row, the only way we know the Name and Account for some transactions is by their location within the table.

Our objective is to repeat the Name and Account labels so that all information about a record is stored within the row, as shown below.

flat data by Jeff Lenning

Since we have to flatten this export often, we’d prefer not to do it manually. The good news is that we can easily set up a Get & Transform Query to automate this on a recurring basis.

Ready? Here we go.

Get & Transform

We’ll accomplish this using the following steps.

  • Create query
  • Fill labels
  • Load to Excel

We’ll work through each step together.

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.

Create Query

The first step is to create a new query. This is really easy. Begin by clicking any cell in the data table, and then select the following command from the Get & Transform group:

  • Data > From Table

If your data is already stored in a table, you’ll see the Query Editor below. If your data is not already stored in a table, Excel will first display the Create Table dialog and then the Query Editor.

Query Editor by Jeff Lenning

With the basic query created, let’s move to the next step.

Fill labels

Now, we need to tell Excel to repeat the labels for the Name and Account columns. We could do this one column at a time or both columns at once. We’ll fill both columns at once. So, we select the Name column, hold down the Shift key, and then select the Account column. The updated query is shown below.

Both Columns by Jeff Lenning

Now, we just need to tell Excel to repeat the column labels. We do this by clicking the following ribbon command:

  • Transform > Fill > Down

The updated query is shown below.

Filled down by Jeff Lenning

Wow!!!!!! I know…right? Totally cool šŸ™‚

Load to Excel

The last step is to bring it back into Excel. We do this by clicking the following command:

  • Home > Close & Load

This returns the flattened data to a new Excel table, as shown below.

Excel Table results by Jeff Lenning

And, the best part is that it is easily refreshed. When new data is pasted into the original blue data table, all we need to do is refresh the resulting green table (right-click Refresh). Excel will automatically update the green table with beautifully flattened data, ready for the next step in your process.

If you have any fun Get & Transform Query applications, please share by posting a comment below…thanks!

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.

10 Comments

  1. Miranda Parks on June 30, 2016 at 5:43 am

    Wow! This trick is simply amazing! Can’t wait to follow more of your time saving post!

    • jefflenning on June 30, 2016 at 6:14 am

      Thanks…I am so amazed by Get & Transform šŸ™‚

  2. Lydia on June 30, 2016 at 2:39 pm

    This is great! I can see the wide applications for the Get & Transform function since lots data Iā€™m working with is stored in pivot tables. The really cool thing is the Refresh function!! Thanks so much!!

    • jefflenning on June 30, 2016 at 3:59 pm

      Get & Transform is my shiny new toy…I love it!

  3. David on July 1, 2016 at 12:09 pm

    As usual your post are timely and are extremely helpful. Pivot tables with slicers are great and the post shows how to get data into tables ready to import.

    David

    • jefflenning on July 1, 2016 at 1:27 pm

      Thanks šŸ™‚

  4. Johne914 on September 25, 2016 at 4:53 pm

    Heya im for the first time here. I found this board and I find It really useful &amp it helped me out much. I hope to give something back and help others like you aided me. eeddebddacka

  5. Marsha on September 26, 2017 at 10:36 am

    SOOOOO COOOOOOOOOOOOLLLLLLLLL!!!!
    Man, I could’ve used this daily a few years ago!
    Thanks for the education, Jeff. Keep it coming šŸ™‚

  6. Vince on April 29, 2019 at 4:52 pm

    Very Nice!!

  7. Sharon Wu on August 27, 2022 at 8:40 pm

    Finally found how I can get rid of those null value. Cool.

Leave a Comment