Excel PowerQuery: Data analysis and preparation often account for almost 80% of the work of a data analyst or data scientist. It is therefore a bottleneck that considerably slows down the work of exploiting the data. Fortunately, there are a number of tools available to reduce this time-consuming activity! PowerQuery is one of the most powerful, and we're going to introduce it to you in this article.
What is PowerQuery?
PowerQuery is a tool developed by Microsoft and directly integrated into some of its applications, in particular Excel and Power BI/ Excel PowerQuery.
It solves many of the challenges faced by professionals when preparing data.
These include:
- Data comes from a variety of non-standardised sources
- Each data source requires specific and often complex processing.
- The use of macros is slow to programme and not very adaptable or reproducible.
- The volume of data is too great to be processed by Excel.
By addressing these various difficulties, PowerQuery provides a practical way of boosting your ETL (Extract – Transform – Load) process.
PowerQuery features
Firstly, PowerQuery allows you to use a variety of data sources. Using the tool, you can connect multiple data sources to the same Excel file. This data can come from databases (SQL, Azure, Oracle, Salesforce), but also from text or csv files or directly from web pages. (small visual if possible)
You can access this option from the Data tab, then in the Recover and transform data section.
Once a data source has been selected, a query is opened to load the data. The Transform button then opens a PowerQuery Editor window.
This editor makes it possible to perform numerous data processing operations that were previously carried out by macros that were slow to program and inflexible.
For example, you can :
- Rearrange rows and columns
- Delete missing values
- Filter rows
- Use the first row as a header
- Merge, split or group columns
- And much, much more!
Once this treatment has been defined, the data is converted directly into a table, ready to be used for pivot tables!
What’s more, once the query has been run, it can be saved and reused many times, just like a macro. However, no knowledge of VBA is required for this tool.
It is therefore possible to completely automate the data processing workflow by combining different queries.
One of the final advantages of PowerQuery is that it doesn’t weigh down your Excel file. In fact, it simply imports data from different sources and transforms it without recopying it in its entirety. This option means you can work with databases containing several million lines!
IF you are planning to use PowerQuery with your database, we recommend that you organise your database properly beforehand to avoid unnecessary effort. To do this, take a look at our article: SQL Tutorial : TOP 5 most useful functions
Conclusion
PowerQuery is a powerful and indispensable tool when working with Excel and Power BI. It is complemented by Power Pivot, a new feature that enables rapid, automated analysis of data that has already been processed. If you’d like to find out more about automatic data acquisition and processing, take a look at our Data Engineer course!
