The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding a way to remove spaces in Excel is a crucial skill for every user. We often can’t see the space characters, but Excel sees them. They … Read more
OK, let’s get started with Power Query. The first thing we need to do is get data into Power Query. In this series, it is our first real look at Power Query; we are also going to use this as an opportunity to explore the user interface. This post is structured into two main parts: … Read more
In this post, we look at how to combine rows with Power Query. The user interface gives us a way to merge columns, but there is no Power Query merge rows transformation. Therefore, I will show you how to achieve it in this post. There are two common scenarios in which we need to merge … Read more
In this post, we explore the topic of Power Query custom functions; What are they? When should we use them? And how to create them? At the time of writing, over 700 standard M functions are already available in Power Query. So why do we need custom functions at all? Custom functions use the existing M function … Read more
In this post, we look at the Power Query Group By transformation. Group By summarizes data by aggregating rows based on one or more columns. In Excel, there are many ways to group and summarize data. We might use techniques such as: In Power Query, Group By performs a similar task, but the purpose is slightly … Read more
In a previous post in this series, we briefly looked at the if statement in Power Query; now, we’re going to dig a bit deeper and understand how the Power Query if statement works. In Excel, IF is a core function; it’s one of the first functions we learn and remains popular for advanced users. … Read more
Formulas are the lifeblood of Excel; they are essential to achieve even basic tasks. On the other hand, Power Query has been designed so that most transformations are accessed through the intuitive user interface. But Power Query formulas exist; it has a formula language with over 700 functions. Each time we make a transformation with the … Read more
Maybe you’ve built some queries in a test environment, and now it’s time to start using them for real. The obvious question arises: How to change source data location in Power Query? Or, maybe you’ve linked to a workbook/CSV file that updates every month, so you need to update the data source. Or, perhaps you’re … 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 a value from another table with Power … Read more
There are many circumstances where we receive information with multiple data points inside a single cell. This often occurs when the data’s original intention is slightly different from how we intend to use it. In these circumstances, we often need to split a cell into its constituent parts. This post will look at solving this … Read more