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
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 its 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 when they … 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
Preparing data is probably the number 1 critical success factor in all data analysis and reporting. If the data layout is wrong, you’ll constantly be fighting Excel; You will have to use very advanced formulas; You will use unnecessary columns and might even duplicate data. Therefore, learning how to unpivot in Excel using Power Query is … Read more