Get & Transform: An Alternative to Manually Flattening Data

Flatten data by Jeff Lenning

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.


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!



This article was written by jefflenning


  1. Miranda Parks

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

    1. jefflenning Post author

      Thanks…I am so amazed by Get & Transform 🙂

  2. Lydia

    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!!

    1. jefflenning Post author

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

  3. David

    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.


    1. jefflenning Post author

      Thanks 🙂

  4. Johne914

    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

Leave a Reply

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

By submitting this form, you accept the Mollom privacy policy.