Power Query in Action: How To Make Data Transformation Easy
By Philip Costa Hibberd
30-03-2023 | Philip Costa Hibberd | treasuryXL | LinkedIn |
Have you ever sat staring at a huge data set, perhaps spread across many files or sources, wondering how to collect and extract the valuable insights hidden within it? Well, look no further because the power of Power Query is there to collect, combine and transform your data. In this blog, Philip Costa Hibberd explains it all and shares with us his moment when he discovered that Power Query is an invaluable tool for saving time and increasing productivity in daily work.
Power Query Explained
Power Query is a data collection, transformation and cleansing tool that comes with Microsoft Excel at no extra cost. It enables you to extract data from various sources, shape it the way you want, and load it into your Excel worksheet for further analysis. The best part? It’s easy to use and can save you hours of manual data processing.
But what exactly is Power Query, and how does it work? In a nutshell, Power Query is a user-friendly editor that allows you to access and manipulate data from various sources, such as web pages, databases, CSV files or even other Excel workbooks. With its simple yet powerful graphical interface, you can perform various operations on your data, such as filtering, merging, and aggregating, all with just a few clicks. The best thing is that Power Query will record in a query all the steps needed to go from input(s) to desired outputs. This is great for audibility, automation and error reduction.
Using Power Query – A Case Study by Philip Costa Hibberd
If you’re looking to save time and increase productivity in your day-to-day work, Power Query can be an invaluable tool. I first discovered its potential during a project for a major trading company in the Netherlands in 2018. The task at hand was to process millions of trade records and compare various data sets to ensure accurate reporting to regulators. Initially, I planned to build a model in VBA, which would have taken several weeks.
However, I decided to explore alternatives and stumbled upon Power Query, which turned out to be the perfect solution. Although Power BI was a well-known candidate, it lacked Excel integration, which was a requirement for the client. Power Query, on the other hand, had the same engine as Power BI but was embedded in Excel, making it an ideal choice.
Within a day, I had a working proof of concept, and within a week, I had completed the first version of the model, which I could easily hand over to the client. Instead of having to walk them through some complex VBA code, I could simply click on each step of the query, and the client could see a preview of how the data was changing. After the handover, the client could also make small changes independently without needing a developer.
Since that project, Power Query has become a go-to tool for reporting, reconciliation, data migration, and modeling projects. It’s also a great starting point for broader automation projects, often used in combination with RPA. By (re)building the data model in Power Query, users can validate and benefit from it early on in the project. Then, RPA (Robotic Process Automation) can be added to automate the model’s refresh, data fetching, and action-taking based on the model’s outcome.
Read also: Robotic Process Automation -the Do’s & Don’ts, by Philip Costa Hibberd
The Benefits of Power Query
Power Query offers a multitude of benefits. Let me share the main ones with you.
- Saves time: Power Query automates the process of data cleaning and transformation, which can be time-consuming when done manually. With Power Query, you can quickly clean and transform data, allowing you to focus on the analysis.
- Easy to use: Power Query has a user-friendly interface that makes it easy for non-technical users to work with data. The step-by-step process of cleaning and transforming data makes it easy to understand the logic behind the transformations.
- Data integration: Power Query allows you to easily integrate data from different sources, such as Excel, CSV files, and databases. This means you can quickly combine and analyze data from different sources without having to manually copy and paste data.
- Reproducibility: Power Query enables you to create a repeatable process for data cleaning and transformation. Once you’ve created a query, you can easily apply it to new data sets, which ensures consistency and reduces the risk of errors.
- Scalability: Power Query is designed to handle large data sets, making it ideal for businesses and organizations with large data needs. It can handle millions of rows of data and still perform quickly.
- Consistency: Power Query allows you to apply the same data cleaning and transformation rules across multiple data sets, ensuring consistency and accuracy in your analysis.
- Customization: Power Query is highly customizable, which means you can create custom functions and queries that meet your specific needs. This flexibility makes it ideal for businesses with unique data needs.
Conclusion
Power Query is a great tool for data cleaning, data transformation and data modeling. It can save time, improve data accuracy and consistency, and enable more efficient analysis.
Power Query is still a bit of a hidden gem in Excel. If you want to try it out, you will find it under the “Get Data” button in the “Data” tab in Excel. From there, you can choose from a variety of data sources, including web pages, databases, XML and CSV files, folders and other Excel workbooks.
There are many resources available online to get you going from there, and our expert Philip Costa Hibberd is always available to answer any questions and provide advice on how to use Power Query to improve your processes. Also, feel free to follow Automation Boutique to stay updated on the latest Excel Power Query tips.
