Power Query Archives - Peltier Tech

You can spend five minutes fixing up your data, or five hours working on a chart with the wrong data.

A user on the Mr Excel forum asked about creating a chart from unsuited data. He asked, “Is there a way to do this without modifying the data table?”

My reply started with “I know you don’t want to hear this, but your data is in the wrong arrangement.” Fortunately, the data was simple and the arrangement was consistent, so it was easy to create a chart staging area to prepare the data for the chart.

Simple Chart Staging Area

I’ve recreated the data below. Obviously someone went to a lot of trouble laying out the data and formatting it just right. This becomes a problem when people become too attached to their fancy display.

The objective is to plot each product by month, for a single year. The chart needs three lines, one for each product. You can tell that it’s not possible using this data directly.

Original data which needs to be staged for the chart.

In general, one would have to rearrange the data, feed it into a pivot table, and create a pivot chart from that. But sometimes you’re lucky enough to be able to write a few formulas instead.

Here is the chart staging area I was able to construct. I listed the months down the side (N3:N14) and the products across the top (O2 to Q2). I put the year in N2, and highlighted it with light gold so a user knew it was important.

Rather than require the user to type in a new year, and possible type an invalid year, I set up data validation in cell N2. Click Data Validation on the Data tab, select List from the Allow dropdown, then select the years in the first row of the original data range (C2:L2).

Data validation dialog to create a dropdown list in the chart staging area

The magic formula is in cell O3 of the chart staging area. It’s a relatively simple INDEX formula with a few MATCHes to find the right cell of the original data range.

=INDEX($C$3:$L$38,
       MATCH($N3,$A$3:$A$38,0)+MATCH(O$2,$B$3:$B$5,0)-1,
       MATCH($N$2,$C$2:$L$2,0))

I created a dynamic title for the chart with this formula in cell N17:

=N2&" Product Sales by Month"

Finally I selected the data range and inserted a line chart.

Line chart created using data in chart staging area

Note that I have included the blank row below the chart staging area. This adds a blank category to the chart with no data points, creating space for the data labels I added to the series. These labels, with font color to match the data points, make it easier to identify the data than the legend, so I deleted the legend.

To get the chart title into the chart, I selected the chart title, typed an equals sign in the formula bar, then clicked on cell N17 and pressed Enter. I also aligned the title with the edge of the plot area.

Create Chart Staging Area with Power Query

Sometimes (most times?) your data will be too complicated or too irregular to use simple INDEX/MATCH formulas to build a staging area. In the old days we would rely on copy and paste and a lot of one-off lookup formulas, but I’ll show how easy it is to stage this data with Power Query.

Rearrange the Data with Power Query

First select your data (or one cell in the data range) and on the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range. If the data isn’t already in a Table, you will be prompted to create one. Make sure the data is correctly identified, and check the My Data Has Headers box. The Table is created, and the Power Query editor opens up and shows the data.

I often remove the step of the query that assigns variable types, because I take care of that later. I selected the first two columns, right clicked in the headers, and selected Unpivot Other Columns.

This gives me the four-column data arrangement I will need for a Pivot-Table-based chart staging area. Here is where I changed the data types of the columns: Month and Product to text, Attribute to whole number, and Value to decimal number.

Then I renamed Attribute to Year and Value to Sales.

Finally I dragged the Year column to the first column of the table.

I clicked Close & Load and landed the query into a Table on a new worksheet.

It’s always better to start with an orderly Table as your data, and base your chart data and any tabulated displays on this.

Create a Staging Area with a Pivot Table

From the Table above, I created a Pivot Table on a new worksheet, with Year in the Filters area, Month in the Rows area, Product in the Columns area, and Sales in the Values area.

I created a Pivot Chart, then added a Slicer based on the Year field of the Pivot Table. It’s very easy to select a year, even easier than with the data validation cell dropdown I used in the first approach.

Recreate the Original Data Layout with a Pivot Table

With the data in a well-structured Table, we can create another pivot table to mimic the original data layout. I put Year in the Columns area, Month and Product in the Columns area, and Sales in the Values area. I chose the Repeat All Item Labels in the Report Layout dropdown on the Pivot Table Design tab.

The same effort that went into formatting the original data can be reproduced on this Pivot Table.

If you work with any type of data, then you know that rarely does it come perfectly in the format you need. Power Query (also called Get & Transform in Excel 2016) is the solution to this problem. Power Query is an amazing ETL tool (Extract, Transform, and Load). If you’re not using it then you’re missing out on the best new feature in Excel.

For a more detailed look at what Power Query is and what you can do with it, check out this Introductory Guide to Power Query.

Power Query can connect to many different types of data, but in this post we’ll take a look at importing data from an Excel workbook and doing some basic data cleansing transformations on some messy data.

The Data

For this post, I’ve prepared some fake sales data (download This Week’s Garbage Data File.xlsx to follow along with the example). It’s presented in a typical fashion which is human readable, but not great for any type of further analysis. In this post, we’ll explore how we can use Power Query to get this data into a standard tabular format which will help facilitate any analysis done later on.

There are a few problems with the data we want to import, and we’ll need to fix them before our data is ready to use.

The data has been summarized by date, but the date pertaining to each set of sales only appears once as a heading.
There are leading spaces in front of the sales person which will need to be removed.
The name of the sales person and their employee ID number have been concatenated together.
There are rows of spaces throughout the data that will need to be removed.
Sales for the different regions have been pivoted across four different columns. We will want our sales in one column with an extra column defining the region.
We have total rows for each of the sections of data that will need to be removed.

Importing Our Data

The first step we need to take is to import our data.

Go to the Data tab and press the Get Data command then choose From File and then From Workbook in the resulting menus.

A file picker menu will appear and we can then navigate to the Excel file we want to import.

Once we’ve picked the file to import, we will be presented with the Navigator window. This will allow us to select the Sales sheet from our workbook which contains the data we want to import. A preview of the data can be seen on the right hand side when selecting sheets.

If our workbook had more than one sheet of data which we wanted to import, we can select multiple sheets using the Select multiple items check box. This would create a separate query for each of the sheets selected and save going through the first couple steps individually.

Now we can either choose to Load the data or Edit it. If we choose to load it, then it will load into an Excel Table with no transformations applied. We want to edit it, this is where we’ll be able to apply our data cleaning transformations. Note: In some builds of Excel, the Edit button is labeled Transform Data.

Press the Edit button and this will open up the Power Query editor.

The Power Query Editor

We are now inside the Power Query editor and we can begin building our data transformation to get our data into proper tabular format.

Any transformations you apply to the data will appear as a step in the Applied Steps area. We can step forward and backward through the query by clicking on any of the steps listed in this area.

On the left side of each step is an X that can be used to delete a step and on the right there is a settings icon which can be used to edit the step.

Getting the Date into its Own Column

The first thing we’ll do in our transformation is get the date associated with each section into its own column. Notice that each of these date headings in our data is separated with a colon. Since no other data in this column apart from the dates have a colon, we can use it as a delimiter to split out the data.

To split this data out, right click on the column heading then select Split Column and then choose By Delimiter.

Select Colon from the list of delimiters and press the Ok button.

Now we have a new column with dates followed by a series of null values until the next date. Right click on the new column and select Fill then choose Down. This will fill the dates down the column replacing the null values with the date from above.

Split Name and Employee Number

In the next step we’re going to split out the sales persons name and their employee number. The name and employee number are always separated by an opening parenthesis so we’ll use that as the delimiter to split the column.

This assumes that an opening parenthesis will only ever appear in our first column when it is separating the employee number.

Now we right click on the first column and select Split Column then choose By Delimiter.

This time we need to use a Custom delimiter since the opening parenthesis is not a standard option. Add in the “(” and press the OK button.

Notice that our new column with the employee number still has the closing parenthesis. We’ll need to clean this up. We can right click on the column and select Replace Values from the menu. Then we can replace the “)” character with nothing. This will remove it from our data.

Remove the Leading Spaces for the Name

Now we can remove the useless leading spaces that were used for indentation in the presentation of the data.

We just need to right click on the column and select Transform and then Trim. This will remove any leading or trailing space characters from our data.

Remove Unnecessary Rows

We’re now ready to remove the unnecessary rows in our data including the total rows and blank rows from our original data file. We also have some rows that are now useless as we’ve already extracted the useful data from them. We can remove all of these rows in the one step.

In fact, we’ll be able to do this by filtering on the first column. Click on the filter icon on the right side of the column heading and then deselect the useless rows (null, Sales for, Sales Person, and Total).

You might be asking why we filtered out the row containing Sales Person. Doesn’t that row contain useful column heading information? Couldn’t we use that and promote it to the column headings?

Yes it does, but we can’t use it to promote to column headers because one of the values in that row is the date from the first step in our transformation. That value will likely change if we’re importing a different set of data with this query.

If we were to promote it to a column heading then we’d have a column heading with the label “2018-01-01”. Any further step that references this column, would be referencing this value which won’t exist. This is were our query would break.

Instead we’ll remove this row and rename our current generic column headings to avoid this problem.

Rename Column Headings

Now we can rename all our columns to their appropriate names. Double click on the current column name to rename a column. We’ll name these Sales Person, Employee ID, Date, North, East, South and West going from left to right.

Unpivot the Region Columns

Now we have an almost tabular set of data. The only thing that remains is to unpivot our four region columns. All the values in these columns represent the same metric of sales and should be in one column with another column that tells us what region the sales amount was from.

Select the North, East, South and West columns by holding Ctrl and clicking on each heading. You can also select the first (North) column then hold Shift and select the last column (West). Now right click on any of the column headings and select Unpivot Columns.

This will result in two columns called Attribute and Value. We can rename these to Region and Sales respectively.

Change Data Types

Now our data is just about ready. At the end of any data transformation, it’s good practice to change the data types of columns to the appropriate type.

For the most part our data has the right data types, but our Sales column can be changed to a currency type. Click on the data type icon on the left side of the column heading and select the Currency type from the menu.

Close and Load the Query

Now that our data transformation is complete, we are going to need to save our query. Go to the Home tab in the query editor and press the Close & Load button. Just remember, you’re still going to need to save the workbook when you’re done in the query editor.

Clicking on the top part of the close and load button will load the data into a new sheet in an Excel table. You can also click on the lower part of the button and select Close & Load To to open the Import Data dialog for more loading options like loading the data directly into a pivot table or into the data model.

For now, we’ll just load the data into a table.

Now we’ve got our data in a ready to use tabular format!

Updating the Query for New Data

Now what happens when we get new data? (Download This Week’s Garbage Data File.xlsx to try this.)

We can use this query again to transform that data. Go to the Data tab and press the Queries & Connections button. Then right click on the query and choose Edit.

Now we can navigate to the Source step in the Applied Steps area.

Replace the source location in the formula bar with the path and file name of the new data and press the Enter button to confirm the changes. Then we can close and load the query. Our data will update based on the new file!

Note, this will overwrite the previous data, so if we want to retain the old transformed data we’re going to need to create a copy of the workbook before performing these updating steps.

Conclusions

Everything we did in this transformation was using the basic point and click interface in the Power Query editor. Once you get the hang of it, making queries to shape your data into the format you want becomes super easy. No workbook formulas, no difficult VBA and no copying and pasting required.

The best part is, if we need to repeat this transformation with another similar data set, then we can update the file path in the query and press the refresh data button and our new data will be transformed and loaded into Excel. Our messy data problem has become a simple refresh.

This is why Power Query is the best new feature in Excel that you need to start using.

About the Author

John MacDougall is a Microsoft MVP and former actuary who currently works on data analysis and debugging code at an ad tech company. He shares his knowledge and passion for Excel and Power BI through his website How To Excel.

Power Query

Prepare Your Data in a Chart Staging Area