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.

[Read more…] about Merge Queries in Power Query – VLOOKUP Alternative

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

[Read more…] about Combine Multiple Worksheets into One with Append Query