In this post, we look at 6 easy ways to calculate how many days are between two dates using Microsoft Excel. As often with Excel, there are many ways to achieve a calculation, so we look at the most useful options for calculating days. Dates can be tricky to work with as there are often Read More
Category: Power Query
Get data from folder in Power Query: combine files quickly
In this post, we use Power Query to import all the files in a folder. We give Power Query a folder path, click a few buttons, and it imports and combines all the files into a single table. It’s like magic! To add another file to the output table, we only have to save a copy of Read More
List files in a folder & subfolders with Power Query
The blog archives contain posts detailing how to list files in a folder (including subfolders) using VBA and legacy Excel 4 function. These posts receive a lot of search traffic through Google, so I know this is an area people are interested in. Using Power Query to list files in a folder is the easiest Read More
Power Query Append: Quickly combine many queries into 1
Data doesn’t always come in one single file, or from one download. Therefore, we have to find a way to stitch multiple files together so we can use it as if it were a single data source. This might be cost center reports, monthly data extracts, product profiles, survey data, etc. Basically, it applies to any Read More
Power Query Parameters: 3 methods + 1 simple example
As Power Query records transformation steps, it includes many hardcoded values within the M code. For example, if we filter a column to select all values greater than 50, the 50 is a hardcoded value in the M code. For something more dynamic, that we can easily change, we use Power Query parameters. Obviously, we Read More
Common Power Query transformations (50+ powerful transformations explained)
We’ve looked at a few Power Query transformations in this series, but now we are going much further. In this post, we look at the simple Power Query transformations available from the main menus. Unfortunately, we can’t cover everything in this post; there are just too many options available. Also, more complex Power Query transformations Read More
Excel rounding vs Power Query rounding: WARNING They are different
The term “rounding difference” commonly describes something insignificant; it rarely raises cause for concern. However, when you discover that Excel rounding and Power Query rounding are different, you suddenly realize that it can be a bigger issue than you first thought. This post explains the different rounding methods in Excel and Power Query, and how Read More
Get to know Power Query Close & Load options
In this post, we are looking at the Power Query Close & Load options. This is part of a series designed to get you up and running with Power Query quickly. So far in the series, we have only loaded data into Excel via a Table. But there are lots of other options to explore. Read More
Use the Power Query editor to update queries
This post looks at how to use the Power Query editor to edit existing queries in Excel. We’ve seen some of this in a previous post of this Power Query series, but now we’ll go a lot deeper. Download the example file I recommend you download the example file for this post. Then you’ll be Read More
Refresh Power Query in Excel: 4 ways + advanced options
Previously, we looked at how to import data from various file formats and load it into Excel using Power Query. In this post, we move on to consider how to refresh that data. Refreshing data is important as it enables us to build a query once and use it over and over (that is automation Read More