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
Automating processes with VBA can involve copying, moving, deleting and managing lots of files. Thankfully, VBA has many built-in functions to undertake these tasks for us. These files do not necessarily need to be Excel workbooks; they can be any file type. Table of Contents Check if a file exists Rename a file Moving a … Read more
Have you ever had to sum the same cell across multiple sheets? This often occurs where information is held in numerous sheets in a consistent format. For example, it could be a monthly report with a tab for each month (see screenshot below as an example). Table of Contents SUM across multiple sheets – basic … 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
Imagine you’ve created a beautiful chart for your Excel report. You decide to hide the source data because the report’s users don’t need to see that. Suddenly the information in the chart disappears. So, this raises the question: how to show hidden data in an Excel chart? We answer this question in this post. But, … Read more
While it is not often that we have to control pictures or images within Excel, based on the number of Google searches for this topic, it’s clearly something people want to know more about. This post serves as a reference to cover most of the scenarios we’re likely to encounter to copy, insert, move, delete … Read more
In this post, we look at how to forecast seasonality in Excel. I have previously written about forecasting seasonality using the FORECAST function. However, with Excel 2016, Microsoft gave us an easier method for forecasting seasonality. Rather than using the FORECAST function with a lot of trickery, we can simply use the FORECAST.ETS function by itself. … 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
Listing filenames in a worksheet, printing all the files to PDF, or making changes to every file in a folder, there are many reasons why we may want to loop through files in a folder. VBA provides us with a few ways to achieve it (1) Dir function (2) File System Object. Let’s briefly look … Read more