Excel with Power Query: The Great Unknown

Are you an advanced Excel user? Have you had to jump through hoops to prepare the data as you wanted? Do you have to integrate other data sources into Excel? Do you know #PowerQuery but don’t know when to use it? If the answer to any of these questions is yes, you may be interested in this article. I deal with many advanced #Excel users who are not familiar with #PowerQuery and its potential, and when they discover it, a whole new world of possibilities opens up for them.

PowerQuery utility for transforming and manipulating data

#PowerQuery is a tool integrated into #Excel since version 2016 (2013 via an AddIn), for data manipulation and transformation (not in the bad sense of the words). Think of it this way, #PowerQuery is also used by #PowerBI, so using it has an advantage: you’re one step closer to using this #BusinessIntelligence tool for your reports and dashboards.

#LowCode programming of data

Add #PowerQuery as your LowCode tool for data manipulation, where step by step you perform actions to prepare the data as you want it. Little steps, using buttons like adding columns, grouping data, combining two or more data tables, formatting data, reordering columns, changing their names, and so on, allow you to get your data source ready to start analyzing.Imagine for a moment that you have three sheets of data to combine, you need to accumulate data, perform operations with different columns, and now think about how you would do it, or how you have done it with formulas because you have surely encountered this. Well, if you’ve gotten chills, relax because from now on you know you can use #PowerQuery. Oh, and keep in mind that you can use cells from Excel itself to parameterize these queries, for example, to indicate date ranges within which the analyzed data must fall.

PowerQuery incredible tool but not interactive

So, if it’s so incredible, what’s the downside? That it’s not interactive, that it doesn’t calculate automatically like formulas do. When you modify cell values, you don’t see the changes in the final result until you do a “Refresh All” or “Refresh Query”. This update can be configured to happen every time you open the file, which is very useful and practical if you need to update from sources external to the spreadsheet.It’s at this point that you have to assess whether you’re interested in using PowerQuery or not, but I can assure you that even though it may seem like you always need calculations at the moment, you can do them all at once and, if necessary, put a “Refresh All” button once the changes have been made.

PowerQuery the step to PowerBI

As I mentioned at the beginning, the queries you make in #PowerQuery can be reused in #PowerBI, allowing you to change this reporting and analytics functionality of Excel for this more efficient #BusinessIntelligence tool.And if you need to change data while viewing the report, don’t worry, #PowerApps integrates with PowerBI to collect data while you view the information, but I’ll discuss that in another article or you can contact me.