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.
[Read more…] about Import Multiple Excel Files from a Folder into a Single Excel FileIn 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.
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;
In this blog post, we look at how to unpivot columns in Excel. The incredible Unpivot Columns feature of Power Query makes it quickly and effortless to 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.
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.
[Read more…] about Unpivot Columns in Excel with the Amazing Power QueryOne 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.
[Read more…] about Combine Multiple Worksheets into One with Append Query
