Category: Power Query

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

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

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

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

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

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

You’ve built some queries in a test environment, and now it’s time to start using them for real. How can you change the queries to point to the live source, rather than the test?  In this post, we will answer this very question. As like most things in Excel, there are multiple ways to achieve Read More

Looking up data from a table or worksheet is probably the most common activity undertaken by Excel users to create reports.  Learning to use VLOOKUP for many is their first taste of Excel’s power.  But what about when using Power Query?  There isn’t a VLOOKUP function, so how can we lookup data from another table? Read More

As we continue through this Power Query series, we have now reached a common and frustrating data format; data stacked in a column.  In this post, we will investigate one basic method for unstacking data. While the example we are looking at below would most likely be created by a person who didn’t know how Read More

Preparing data is probably the most critical success factor in data analysis.  If the data layout is wrong, you be continually fighting Excel; having to use advanced formulas, adding unnecessary columns or even duplicating data.  But if the data is in the right structure, Excel becomes easy.  I used to be amazed at people who Read More