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
In previous posts in this series, we’ve seen how to import external data from a single file, import all the files in a folder, and import data from a Table or range within the same workbook. But what if we want to import ALL the data within the same workbook? We are looking at that in this … Read more
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
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
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
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
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
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
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
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