Power Query is a powerful business intelligence tool inbuilt in Excel, that allows you to import data from any number of sources, clean it, transform it, then reshape it accordingly. This once off created query can then be re-used over and over again by simply refreshing it.
It is a feature that started out as an add on for Excel 2010 and 2013. It has since become a fully inbuilt feature in later releases. Also known as the Get & Transform layer this same technology also features in Microsoft’s core business intelligence tool, Microsoft Power BI.
POWER QUERY STAGES
There are 4 main sequential stages to using Power Query:
1. Connect Stage:
With Power Query you can import/connect to data from external sources.
2. Transform Stage:
Using the interface provided you can write/generate M Language steps to shape the data as you require.
3. Combine Stage:
Potentially you can look to integrate data from multiple sources together to create a single merged data view.
4. Load Stage:
The final query can then be loaded either directly into a worksheet or into the Data Model. This query can then be periodically refreshed.
All this functionality allows for a single Excel file to tap into data from multiple other sources (databases, Excel files, CSV files, text files, Word files, RSS feeds etc…) in a robust performance optimised way. Far beyond the capabilities and performance of other/previous methods.
ACCESSING THE POWER QUERY INTERFACE
There are multiple ways of accessing the Power Query interface, here is one: Data > Get Data > Launch Power Query Editor
A CUSTOM-BUILT USER INTERFACE SOLUTION USING POWER QUERY
THE REQUIREMENT
A client came to Olas with a problem that needed to be solved. They were looking for an easy and fast method to merge data from 2 different Excel files together into a single consolidated view. This was a process that would need to be replicated regularly and needed to be flexible as the source files would be different physically files each time. The name of the workbooks and the source worksheets would change month on month but the format of the files would remain the same.
But the next requirement was key; they also required an interface to be created from where a non-technical user could easily change the source of the workbook/worksheets to pull from different iterations of the same file.
THE SOLUTION
Power Query was the obvious ideal solution for connecting to the 2 different data sources, to modify the formats of the extracted contents to match each other and then to merge the data together.
Olas first created a simple user interface to allow the end-user to browse to an import folder, to set the names of the workbooks in question and also to set the names of the worksheets from where the data was to be imported from. VBA Macros were added to enable the functionality of the Browse button and the Refresh button but these macro elements were not essential to the project.
The Power Query elements were able to be developed in such a way as to be flexible enough and to take the source folder, filenames and worksheet names from the custom created interface in Excel. Other information such as an Import Ref, import date was also added to the records imported.
Upon clicking the Refresh button, the code would run the Olas created Power Query queries automatically and within second the final merged dataset was returned to the user on screen. What would of took hours to complete manually could now be achieved with easy and in minutes. All this without the need for the end user themselves having to even know anything about Power Query.
In this particular project the final merged dataset was also brought through Excel’s files Data Model from where it was further manipulated by Power Pivot before loading the final reporting elements to the screen for the user.
Excel’s Inbuilt Technologies Used as part of the Project:
- Power query
- Power Pivot
- VBA Macros
CONTACT OLAS
Do you have an Excel project where data needs to be pulled in from external sources? If so, Power Query could well be the answer! Contact Olas today to discuss how we might be able to assist your project.
