In Excel, we can easily create absolute and relative references. A simple $ symbol here and there is all it takes. But Power Query is less flexible, therefore getting values from other rows is not as simple. So, in this post, we cover how to get Power Query absolute and relative references. The filename for Read More
Category: Power Query
Auto refresh Power Query on parameter change
A few days ago, a reader asked a question about using parameters in Power Query; they wanted to auto-refresh Power Query whenever a parameter changes. “One question, how do you set the query to refresh automatically when a value changes.” I’ve not tried this before, but I can understand the benefits. It reduces the need Read More
Power Query – Percent of total or category
In this post, I want to cover how to calculate the percent of a total in Power Query. We’ll also take this one step further, to consider how to calculate the percent of a category. In standard Excel, these calculations are simple, as we are so used to the formulas to achieve this. But for Read More
Power Query – Running Total
In this post, I want to cover a common reporting requirement, which is a Power Query running total. I have previously covered a similar topic in a post about running totals in tables; however, those methods don’t work in Power Query. So, we’ll need to find an alternative approach. Both Tables and Power Query were Read More
Power Query – Tips and Tricks
It’s now time to bring this Power Query series to a close. And where better place to end than with some tips and tricks to help you to succeed. Change the default Close & Load options Power Query’s default Close & Load options assume we want to load the data into an Excel table. As Read More
Power Query – Common Errors & How to Fix Them
I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has it’s own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do Read More
Power Query – Custom Functions
In this post, we’are going to explore the topic of custom functions; What are they? When should we use them? And how to create them? At the time of writing, there are over 600 standard functions already in Power Query. Yet, those functions won’t always do what we need, or in the most efficient way, Read More
Power Query – Grouping and Summarizing Data
Looking back at this Power Query series, I can see that I have omitted to post about one of the more common and more powerful features; which is Group By. We have used it a few times already, but in this post, I wanted to give it a more detailed look. In Excel, there are Read More
Power Query – If Statements for Conditional Logic
In a previous post, we briefly looked at the if statement in Power Query, now we’re going to dig a bit deeper and understand how it really works. In Excel, IF is a core function, it’s one of the first ones we learn. Last week, we looked at Functions in Power Query, but we didn’t Read More
Power Query – Formulas
Formulas are the lifeblood of Excel; they are essential to achieve even basic tasks. Power Query, on the other hand, has been designed so that most transformations are accessed through the intuitive user interface. But Power Query has a formula language, with over 700 functions. It is there so that we can tackle some of Read More
