Excel Power Query

The intelligent excel tool to structure, load, share and analyse data, Power Query, is one of the most powerful features of modern-day excel.

If you are eager to learn how it works, you are in the right place.

Below is a comprehensive guide on Excel Power Query; what is it, how it works, and everything that you’d want to learn about it.

Learn more about other advanced tools of excels by enrolling for our Excel course offerings.

 

What Is Power Query?

The power query tool of Excel facilitates the smooth import of data from different types of source files. It enables easy transformation of data and makes it usable.

In simplest terms, with power query for excel, users can import external data into their excel sheet and then transform it to their needs. Transforming data includes adding or deleting columns, filtering data, changing the data type, merging tables, and much more.

Once the rules for sorting the data are set up, users only need to refresh the query every time the data needs to be updated. There are three main phases to the function of Power Query.

 

The Microsoft power query for excel was first introduced in MS Excel 2013 as an add-in which was also made available for Excel 2010. Once downloaded and enabled, it appeared as a separate tab of the Ribbon named ‘Power Query’.

However, since the launch of Excel 2016 and versions there onwards, the Power Query tool comes embedded in Excel as ‘Get & Transform Data’ on the Data tab. The commands on the Data tab are rearranged to enhance the import and cleaning of data in excel.

You can use the very powerful Excel Power Query tool to connect to external data, import voluminous data, and transform it, and use it in Excel in various ways.

What makes the Power Query tool outstanding is the feature that allows users to automatically reapply the same data transformation steps to other data sets. Users who have to work out monotonous data files can save several hours using this smart feature.

The power Query editor is also available in Power BI desktops. Learn more about Power BI here.

 

Power Query Use Cases

Power Query is widely used to import, cleanse, transform and consolidate data.

It is mainly used to process large sets of data that are imported from various sources through manipulation and collation of data from different source files including text, CSV, Excel and pdf files.

For instance, a business with its operations spread across multiple regions might have to consolidate its accounts periodically.

Summary of accounts prepared in different regions might be in the functional currency of that region. Currency translation and accounts’ consolidation each month can get really hectic and sometimes, erroneous.

To help the cause, you may employ Excel Power Query. It can help you with the import of multiple files in different formats. You can then apply different transformations including change of currency and formats and load it together to Excel to consolidate final accounts.

It is beneficial to deal with such data with Power Query as it doesn’t change the data source file, but the data is only cleansed and imported in Excel as a snapshot. Any changes that you make to the source file are automatically updated in the Excel sheet when refreshed.

Also, even if you source hundreds of thousands of rows of large data through Power Query, it won’t make your Excel file heavier as the data is not directly imported to Excel.

Power Query goes hand in hand with VBA. Both of these more advanced features in Excel are very useful – and you can master VBA In Excel here.

 

Importing Data With Power Query

The power query tool allows users to import data from a variety of sources. Some of these sources include the following.

 

Importing data in excel using the Power Query tool is simple enough. The import method might slightly vary with the kind of source file from where the data is being imported. However, the basics remain the same.

If you want help visualising this data, read our guide on Stacked Bar and Column Charts.

Let us see how you may import data using Power Query from a workbook.

 

Step 1:

Go to the Data tab on the Excel Ribbon.

 

 

Get & Transform Data > Get Data > From File > From Workbook

 

 

Step 2:

This opens up a Browse Dialogue Box as below. Navigate to the location from where you want the workbook to be opened.

 

 

Select it and click import.

 

 

This should open up the Navigator window as shown above. Click the file to preview the contents. Tap Load if you simply want to load the data into excel or choose to Transform if you want to transform it before it’s loaded in excel.

 

Let’s look into another example of importing data using Power Query from a text file.

Here is an insight into the text file that we want to be uploaded to Excel.

 

 

For more on Excel, read our guide on Forecasts in Excel here.

To tabulate the given data in Excel, follow the steps below.

 

Step 1:

Go to the Data tab on the Excel Ribbon and select Get & Transform Data > From Text/CSV

 

 

Step 2:

This opens up a Browse Dialogue Box as below. Navigate to the location from where you want the text file to be opened. Excel would only identify and show files with the .txt extension.

 

Select it and click import.

 

 

This should open up the Navigator window as shown above.

Click the file to preview the contents. When importing data from a text file, you’d have to specify the delimiter i.e. the basis upon which you want the columns to be split.

In the given file, we have chosen ‘Tab’ as the delimiter.

Tap Load if you simply want to load the data into excel or choose to Transform if you want to transform it before it’s loaded in excel as below.

 

 

You could also try several other transformations to the subject data like transposing. Learn more about the transpose function of excel by reading this guide.

 

How To Use Power Query – Simple Example

Unlike other tools and functions, Power Query might give Excel users a little tough time. If you are a novel user with no prior experience, you may need to look into how Power Query works before you can use it to your advantage.

Below is a simple example of using Power Query in Excel. Let’s dive straight in.

The image below manifests the data that we would load to Power Query and publish the same to Excel.

It is an Excel Book that consists of the clients’ detail of a business and the number of hours spent by each member of the reporting line working for each client.

 

 

To import this data to excel, here are the steps that need to be followed.

 

Step 1:

Reach out for the Data tab on the Excel Ribbon > Get & Transform Data > Get Data > From File > From Workbook

 

 

Navigate to the folder where the Excel file is located on your PC, select it, and press ‘Import’.

 

 

Excel imports the data to Power Query as follows:

 

 

Step 2:

Once the data is loaded into Power Query, cleanse the data as you may like. Here are a few exemplary transformations that you can make to your data before it is loaded in excel.

 

1. Filtering Data:

For instance, the data loaded above have certain rows ‘null’. If you want to remove the partially null rows, you may filter them out as follows.

 

2. Naming Column Headers:

Similarly, if you want each column to be named by its header like the ‘Client Name’, ‘ Client ID’ etc. You may use the first row as headers by choosing the said option from Home > Transform > Use the first row as headers.

 

 

Don’t forget to observe how excel has saved it as an applied step that can be automatically applied to further data imports.

 

 

3. Removing Columns / Rows:

You may also want to remove any column or add a new column. For example, you may remove the last column ‘Miscellaneous’ by going to Home > Manage Columns > Remove Columns.

 

 

4. Splitting Columns

Another important feature that Power Query offers is that of splitting columns.

For example, in the given data set, the Engagement Letter Nos. represent the reference number of each Engagement letter. The last part of these numbers exhibits the year of engagement inception.

To make a separate column of Engagement Inception year, you may split the column by the relevant Delimiter (i.e. the ‘-‘ that separates the year).

 

 

Specify the character from where you want to split the column as follows. Also, as the delimiter ‘-‘ appears in each value two times and we only want to split the rightmost value being the year, select the option of Right-most delimiter.

 

 

Below is how Excel has split the values after the dash.

 

 

Step 3:

Once you’ve transformed the data to your choice, Excel would save all the steps performed by you as Applied Steps in sequential order.

Press Close and Load once you are done to have the data loaded to Excel.

 

 

Excel loads the data from Power Query into excel.

 

 

Viola! Now you have succesfully imported it, think about Analysing Your Data In Excel.

 

Troubleshooting with Power Query

Power Query is an advanced tool of Excel that can ease your job by a hundred times. This advanced tool can sometimes get tricky to use and may put up undue errors. Some common errors faced by Excel Users with Power Query are as follows.

 

1. DataSource Error

As the name verily suggests, this error relates to the source file of the data you’re trying to import. Excel might pose this error when the proposed data source is either inaccessible or has been moved from the browsed location.

For example, some files on your PC may be restricted by a user. Or they may be placed on a disk space that is only accessible by a particular user. Importing this file will give a DataSource error.

A quick solution to this is to make the file accessible by seeking restriction removal from the authorized user. Or change the path of the file to have it stored in a disk you’ve access to.

 

2. Setting up queries

Setting up queries in Power Query needs some thought, particularly when you need to refresh them time and time again. Queries will only work effectively when set up in a logical order starting from filters and error handling steps up till the addition of columns and other transformations.

 

3. The Undo Option

Users who come across Power Query for the first time find it disappointing to see nothing happen upon pressing ‘Control + Z’. You can’t undo your previous action in Power Query that way, but by removing your last applied step.

Everything you do in Power Query is saved as an ‘Applied Step’ and appears in that small window on the bottom right. Move your cursor over the relevant step, and on the left would appear a small cross as follows.

 

 

Click the cross to cancel the last performed step and Undo the undesired action.

For more troubleshooting tips, read our guide on Auditing Formulas here.

 

Conclusion:

The above article should give you an insight into how Power Query works. You can use this intelligent and advanced function for multiple purposes. There is much more to Power Query to unleash – keep practicing, and you’ll become a pro in no time.