Category: Blog

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

The web is a vast place with so much information.  However, just because the data is there does not make it easy to get at  In this post, we’ll look at how we can load web data into Power Query.  While you might get excited at the thought of absorbing data from everywhere with the Read More

We’ve seen how to import external data from a single file, how to import all the files in a folder and how to import data from a Table / named range within the same workbook.  But what if we want to import ALL the data within the same workbook?  Well, that is where we are Read More