Power Query VBA: Both can automate data transformation tasks in Excel and Power BI. Find out all you need to know about creating macros!
To import and transform data in Excel and Power BI, the Power Query engine is commonly used. This tool enables data to be manipulated from external sources.
However, many users are unaware that it is possible to use the VBA (Visual Basic for Applications) programming language to create data transformation automation macros.
It’s an excellent way of making data manipulation tasks more efficient. Find out all you need to know on this subject!
Power Query VBA - What is VBA?
The VBA (Visual Basic for Applications) programming language is directly integrated into the tools of the Microsoft 365 suite.
It lets you easily create macros to automate the most repetitive tasks in software such as Excel, Word, Access, PowerPoint and Outlook.
What is Power Query?
Integrated with Excel, Power BI and other tools in the Microsoft Power Platform ecosystem, Power Query enables data extraction, transformation and loading (ETL).
In particular, it is used to load, clean and transform data from a variety of sources. These include Excel, CSV files, databases, cloud services and web pages.
Data transformation operations include filtering, sorting, grouping, column splitting and grouping. Custom columns can also be added using Power Query formulas.
To create relationships between tables, they can also be linked using foreign keys. The tool also allows you to create dynamic queries to update data as it is added or modified in the external source.
Power Query’s strong point is its intuitive, easy-to-use interface for importing and transforming data. However, repeating the same transformation tasks over and over again for different sources can quickly become tedious. The VBA language offers an alternative.
Why use Power Query VBA together?
By using VBA in conjunction with Power Query, it is possible to automate data transformation tasks with the aim of increasing efficiency.
The first step is to create a Power Query, via the Excel user interface or the Power BI editor. Once the query has been created, it can be saved as an automation macro that can be freely modified. Here are a few examples of use cases.
Firstly, the process of importing data from different sources can be automated with VBA macros. You can then transform the data with Power Query to make it more usable, and create visualizations with Power BI.
For data cleansing tasks, VBA can, for example, automate the deletion of duplicate rows and unnecessary columns, replace missing values, or convert data into different formats.
VBA can also be used to combine several data sources using Power Query. For example, several Excel sheets can be combined into a single query.
Similarly, data from different external sources can be combined. For example, a CSV file can be combined with a database.
There’s nothing to stop you creating a VBA macro to create Power Query connections for several Excel tables. This saves time compared with manually creating a connection for each table to be combined.
Another use case example is the use of a VBA macro to create a refresh button, enabling the user to automate queries directly in Excel or Power BI.
As a general rule, all repetitive transformation tasks can be automated with VBA. Its use is therefore highly relevant for processes to be carried out with Power BI on each new data source. However, there are limits to this synergy.
Power Query VBA - What are the limits?
VBA macros are very useful, but cannot exceed a certain level of complexity. A data transformation task that is too arduous requires a complicated macro that will be difficult to maintain properly.
Similarly, Power Query is a powerful data manipulation tool, but does not support certain data sources. It is also limited in terms of data transformation.
On data sources with complex relationships between tables, automating transformation tasks can also be difficult.
Alternatively, more advanced data manipulation tools such as Python, R or SQL can be used to create automation scripts.
Power Query VBA - Conclusion
The VBA language is very useful for automating data transformation tasks on Power Query for Excel and Power BI. It is a valuable asset for the most repetitive processes, although it does have its limitations for complex manipulations.
To learn how to handle Power Query, VBA and other advanced Excel or Power BI tools, you can choose DataScientest.
Our intensive Power BI training takes place over 38 hours, spread over 5 days. The first 3 days are dedicated to introductory training for beginners, and the following 2 days are designed to reinforce your expertise and are aimed at initiates.
You can take the whole course, or choose one of the two parts depending on your needs and skill level.
By the end of the course, you will have fully mastered Power BI and be able to use it to collect, transform and analyze data and create interactive dashboards. DAX, M, VBA and the Power Query engine will hold no secrets for you.
All our teachers are Microsoft Trainer certified, and our organization has Microsoft Learning Partner status. This means that we can accompany you all the way to Microsoft PL-300 certification to become a Power BI Data Analyst Associate!
The program is completed entirely by distance learning via the web, and we are eligible for funding options. Discover DataScientest!
