This tutorial will take you through the new Power Query custom data types in Excel.

What does this all mean?

Well, you can now create your own data types in Power Query similar to the ones you see on the Data tab (Stocks, Geography, etc).

These rich data types allow us to store many columns of data in just one column. This frees up space on the worksheet.

All columns of data are easily accessible using formulas or by simply typing the column name.

In this tutorial, you will see how to create Power Query data types and how to access their data with formulas.

In this post, we look at how to import multiple Excel files that contain multiple sheets into one Excel table.

Importing multiple files from a folder is one of the most popular functions of Power Query. A common question I get is “what about if an Excel file had multiple sheets”.

In this post, we answer that question.

You can download the files used in the tutorial to follow along.

Watch the Video

IF functions are some of the most popular formulas in Excel, and in this blog post we will look at how to write an IF function in Power Query.

There are some key differences between IF function in Power Query and their worksheet equivalent, but you can pick them up quickly.

In these examples we will use the sample data below, already loaded into the Power Query Editor.

In this tutorial, we look at how to import multiple Excel files into Excel from a folder using Power Query.

It is amazing how simple this process can be with Power Query and modern day Excel. However, it can still come with the odd hiccup.

In this example, I want to import all the Excel files from the folder shown below.

Ideally this folder would contain Excel files ONLY. But when you have teams of people working on a shared area, things happen.

Somehow a PDF file and a JPG (picture of me) have appeared in the folder.

This can create complications with the query we will create to import the Excel files. So we will build in some protection so that the process works seamlessly. Both for now, but also for future imports.

In the future, it will be a case of clicking the Refresh button. And any changed files, or additional files will all be imported again without complication.