Listen on Spotify.
Remember the last time you spent all day working on a huge data set, cleaning it up? With Power Query, your troubles may become a thing of the past.
Power Query is a powerful data automation tool you can find in Excel, as long as you have at least Excel 2010. Power Query lets you import data to Excel from various external sources. You can then clean up the data and prepare it for use and analysis. Along with these useful shortcuts, this will supercharge your productivity and enable you to get data ready in almost no time at all.
Later, we’ll look at the steps you need to take to get data, transform it and load it in Excel. First, though, let’s list the chief benefits of using Power Query.
Let’s get into it.
Main benefits of using Power Query
Here’s what it can do for you, to make your life easier and speed up data handling and analysis.
You can get data from different places
You can import from SharePoint, other workbooks, text files, folders, and even PDFs. You can connect to the web, different databases and your Outlook email account and online services. You can work with huge amounts of data, you’re not limited to a set number of rows as in Excel.
You can clean and transform data
This makes it possible to extract meaningful information. You can shape data easily by clicking a few buttons. You can:
- Compare two datasets to one another and identify differences between them.
- Split data in a single column into multiple columns or merge many columns into one.
- Extract numbers from text.
- Calculate hours worked.
- Sort data using filters.
- Add calculations.
Loading data to analyse and use as a foundation for reports
You can load your data in the form of a table, or you can add it to the data model. Power Query and Excel’s data model work in tandem so you can create relationships between many different tables.
Just investing a little effort will help supercharge your productivity
Usually, the more effort you put into something, the more you get out of it. This is not the case with Power Query. Just by learning a few things, you can speed up your work and significantly boost your efficiency. Best of all, you don’t need to know a thing about coding to use it.
How to access and use Power Query
If you have Excel 2010 or Excel 2013, you need to install an add-in to use Power Query. In later versions, you can navigate to the Data tab to find the Get and Transform Data category. When you click on Get Data, you’ll see a list of all the sources you can extract data from.
Choose where you want to pull data from. For now, let’s imagine you want to get data from a folder.
You will be prompted to add the file location. Then another window will pop up showing all the files in that folder as sheets. You’ll have the option to load from the one you want or combine all the data into one sheet. Click on Combine Data, then choose Transform Data. Selecting that option lets you change the data before importing it into Excel. Alternatively, you can combine and load the data into Excel or into another location. The problem with loading the data somewhere else is that you lose your chance to modify it.
When you click Combine and Transform in the drop-down menu, another dialog box will pop up. It asks you which file you want to pull data from. The default setting selects the first file. You can see a preview of your data to make sure the extraction process has worked properly. When you’re ready, click ok.
Your data will be imported into Power Query. On the left, you’ll see all of the different queries you can use, including Helper Queries and a Transform Sample File option. The last query you can see will apply all the changes you make in your data so that it will be modified when you bring it into Excel. The final Power Query is highlighted in green.
On the right side, the various query settings are displayed. You’ll also see Applied Steps. This is similar to running a macro in Excel. It’s how Power Query records every change or transformation that you apply to the data. You can also use the Ribbon at the top to transform the data. If you have a big data set you’ll only see the first 1000 rows. Don’t worry though. Once you make changes to your data, you can close it and load it, which will apply the modifications to all of the data, including the unseen portions of it.
Here are a few more things you can do when you transform data.
- Remove or insert columns.
- Filter out bits of data you don’t want to analyse.
- Change the headers of any columns you split. For example, you might choose to separate a customer ID number from a customer name, and then give it a more descriptive header.
Here’s how to apply a calculation to your data. Let’s say you’re working with sales data and you want to calculate the profit.
- Select the relevant columns, for example cost and revenue.
- Click on Add Column in the Ribbon.
- Click Standard and select the type of calculation you wish to apply. In this case, we want to subtract the cost from the revenue to get the profit. Choose subtract.
- This will add a new column called subtract. Now rename the column to something that makes sense. In this case, profit would work fine.
- Drag the column to where you want it in your data set.
- If you want to show the cost, revenue and profit as currencies, select the columns and right click.
- In the dropdown menu, click Change Type, then click Currency. You’ll now see a currency sign in the header of the columns you selected.
Imagine you want to calculate something using dates. You can select the appropriate data and go to Add Column. This time, instead of going to Standard, click on the Date button. You’ll find the option to subtract days. This would work well if you wanted to work out how many days it takes for an order to be shipped.
If you want to check Data Query is remembering your actions, look at the right-hand side of your sheet. You should be able to see every step you took listed under Applied Steps. You can undo any steps recorded here by hovering your mouse over it and clicking on the X that appears.
When you’ve completed all of your transformations, click the Home tab and click Close and Load. You’ll be taken to Excel. All of the data from your separate workbooks should now be combined, and all of the changes you made in Power Query should have been applied.
You know what the best part is, though?
You’ll see a pane listing Queries and Connections. When you right-click Power Query you can go down the menu and choose Show the peek. This will show you a sample of the data and the data source. If you were to add files to the folder shown as the data source, the new data will be incorporated into the data set automatically. All your transformations will be applied too. That’s bound to save you loads of time and energy.
Soon, we’ll go through what you need to do to add new data to your Pivot Tables and Charts. Next though, let’s find out how you can insert Pivot Tables and Charts to start analysing your data.
Pivot Tables and Pivot Charts
Now you’re ready to create a Pivot Table and Chart to start examining your data. To create a Pivot Table:
- Click anywhere within your data.
- Click Pivot Table, then click ok in the dialog box that comes up.
- Go ahead and build your table based on the data you want to track and analyse. Imagine you need to track profit by year.
- Move profit into the values box and drag the order date into the rows box.
- This will create a table showing profit by year.
You can create a visual representation of that information. Pivot Charts are perfect for this. Here’s how you make one.
- Click within the table. A tab called PivotTable Analyze will appear.
- Under that tab, you can click Pivot Chart. Click on it and choose what kind of chart you’d like. Then click ok.
- Hey presto! You can now see at a glance what your profit was by year.
Adding new data into Pivot Tables and Charts
As I pointed out earlier, as long as you add new files into the same data source as recorded by Power Query, it will be added to your existing data with all the same changes made to that new data.
This means that if you’ve created a Pivot Table showing your profit by year, and new data for another year is added to the folder, you can easily update your table to include it. Just click into the table, go to PivotTable Analyze, then to Refresh. Finally, click on Refresh All. The new data will be included automatically. The Pivot Chart you created will update too.
If you want to check that everything has worked as it should go back to the original datasheet. If you click on the filter on the order date, you will see that data for a new year has now been added.
You can spend the time you’ve saved doing high-value tasks that will drive results, like analysing the data and creating reports that are sure to impress.
The wrap up
We’ve covered what Power Query can do for you to help save time and effort and supercharge your productivity. Start experimenting today to easily retrieve data, transform it and load it into Excel ready for analysis. Soon enough, you’ll be analysing data like a pro and taking your Excel skills to the next level.