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
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. Table of Contents Open the Power Query editor Queries & Connections Launch Power Query editor Query ribbon … Read more
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
The party was already in full swing; I was going to be late. Not fashionably late, just late. When I finally arrived, rather than the usual “What time do you call this?“, I received the “Hey; you made it, that’s amazing. It’s great you could come. Let me show you around“. Nobody looked down at … Read more