Import Multiple Excel Files from a Folder into a Single Excel File

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 of the Excel files from the folder shown below.

Folder containing multiple Excel files

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. Continue reading

Excel Power Query – VLOOKUP using Merge Queries

In this blog post we explore how to replicate a VLOOKUP in Power Query using merge queries.

Excel users love VLOOKUP. It is one of the most useful functions there is. So it stands to reason that we may need to perform similar tasks in Power Query.

In this blog post we will look at performing common VLOOKUP scenarios by using merge queries within Power Query. And also discuss some of the differences (advantages of Power Query) between the two.

Advantages of using Power Query over VLOOKUP

Now Power Query may not always be the route I would take. Sometimes I just need quick answers. So I would bash out a quick VLOOKUP to get the results, and paste to values (if needed) to remove the formula.

Power Query has many advantages over the classic VLOOKUP function however. Simplifying tasks that with VLOOKUP would be awkward, or in some cases not possible.

Here are some of its advantages;

  • Power Query can work with data from external sources directly (CSV files, databases, web queries) without first dumping the data into a worksheet. This can keep your workbooks lean and mean.
  • In Power Query we could easily return multiple columns of data for the lookup value without the need for a separate VLOOKUP for each column.
  • We can lookup based on multiple columns/conditions using the Power Query approach without needing complex array formulas or helper columns.
  • Other steps could be added to Power Query to apply formatting, or remove bad rows.

Continue reading

Unpivot Data in Excel with the Amazing Power Query

In this blog post we look at the incredible Unpivot Columns feature of Power Query in Excel to quickly unpivot data.

Power Query allows us to transform messy data into something meaningful and useful. And better yet, it is quick and easy to do so.

The Unpivot Columns tool within Power Query is one of its greatest assets. So lets have a look at an example of its use.

Our mission is to convert the table on the left, into the table on the right in the image below.

Unpivot data in Excel with Power Query

The table on the left is a typical example of data that we may receive when downloading or exporting out of a system. It is already in a pivot format. It looks neat, but is not very useful.

We want the data in a tabular format so that we can easily analyse it further using Excels power. For example, in our own PivotTable. Continue reading

Combine Multiple Worksheets into One with Append Query

One of the most common questions to receive in training is to consolidate or to combine multiple worksheets into one.

The best options available to do this are the Get and Transform features of Excel (known as Power Query in previous versions), or to create a macro using VBA.

In this blog post we will explore the Append Query feature of Get & Transform.

We have a workbook which contains 4 worksheets named France, UK, Spain and Italy. Each one contains sales data. The scenario is to combine these into a single worksheet for analysis.

We want to imagine that this is a regular process (maybe weekly). This data is downloaded or received in some way from an external source. And it needs to be combined into one worksheet quickly and easily. Continue reading