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.
The data we receive is not flat, as shown below.
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.
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.
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.
With the basic query created, let’s move to the next step.
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.
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.
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.
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!
- Sample file: FlatGT
- Manually flatten data: http://www.excel-university.com/how-to-flatten-repeat-and-fill-labels-down-in-excel/
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.