Microsoft Excel – Power Query

A review of the process used to develop and update modern business intelligence solutions in Excel.

An introduction into using Power Query technology to get data for an Excel business intelligence solution.

While there is certainly value in the initial analysis, the real value in any BI solution comes from the refresh. When your boss comes to you and asks you to update the solution it took you hours to build and gives you a deadline of 10 minutes, you need to be able to say “Yes, I’ve got this.”

One of the things we need to understand is how Excel interprets dates, and how we can override the defaults by setting the data’s Locale.

Importing data from a text file into an Excel table.

Data often lives inside Excel tables. But even if it doesn’t, Power Query will create one for you.

Sometimes you don’t want to add a table style to your document. In this lesson we’ll look at how to import data from an Excel range.

Sometimes you want to build a solution that pulls its data from a different Excel workbook. In this lesson we will show you how.

One of the most important data sources to pull from is a database, but they have their own interesting nuances as well. In this lesson we will take a look at those.

Appending tables is the process of stacking them vertically to create long tables that can be used to feed business intelligence solutions and PivotTables.

Power Query adds the ability to reach into a folder and append all files in the folder based on the transformations of a single file. Before you can leverage it, however, you need to learn the background, and that’s what this lesson is all about.

Before you unleash this magic skill, there is a bit of prep-work that should be done to future-proof your solution and make it more portable. In this lesson we will explore how to do this.

This lesson will teach you how to unleash the magic of appending all files in a folder, and show you how effective it is.

Wouldn’t it be nice if you could append all Tables in the current Excel workbook without having to do each manually? You absolutely can – and this lesson shows how to avoid causing massive errors in the process.

This lesson explores connecting to an external workbook to append all tables, worksheets and/or ranges within it.

In this lesson we will explore seven different ways to find matching records, all without using a single VLOOKUP!

The lesson demonstrates how to use Power Query to replicate VLOOKUP’s exact match functionality (without writing any VLOOKUPs!)

Since there are so many joins, it really pays to explore them all. In this lesson we will look at the Left Outer, Right Outer, Full Outer and Inner joins, as well as show a trick for joining on composite keys.

Some of the most powerful joins for those trying to perform reconciliations are those that identify the items that do NOT match. In this case we will explore how the Left Anti, Right Anti and Full Anti joins can be used to accomplish this.

No data manipulation tool would be complete without the ability to use conditional logic. In this lesson we will explore the benefits of Power Query’s user interface driven “Conditional Column” tool.

Unfortunately, not every conditional logic scenario can be created using the “Conditional Column” tool. In this lesson you’ll learn about the shortcomings of this tool and how to work around them by using the “Add Custom Column” button.

This lesson shows how we can use errors to our benefit to solve conditional logic problems.

Sometimes trying to extract data from a column can be difficult, so why not let Power Query help? Microsoft has put significant effort into a tool called “Columns From Examples” where you can define and refine the output, and let Power Query build the formula to get the results.

If you work with PivotTables a lot, you know that they put information out the way users want to read it, but you can’t use it to drive another PivotTable. So what if there was an easy way to unpivot the data? There is, and it’s called Power Query!

The challenge with the Unpivot functionality is that you can only unpivot based on a single header row. So what if you have multiple rows of subheaders? In this lesson we will show you how to circumvent that issue.

While we can use PivotTables to pivot data, occasionally we need to pivot our data in the data preparation phase. This lesson exposes how the Pivot functionality works in Power Query.

This lesson exposes a very important pattern for wrangling data. Once you can get it into a single column, hit it with this recipe and it will land nicely into a table.

When building models, many users pull in data that is far too granular, resulting a many more records than they need. Power Query offers a tool to group that data for you, so that you aren’t wasting storage space on unnecessary records.

This lesson shows how to create query groups so that you can keep your queries organized. Super useful for creating a self-documenting query chain.

If you ever need to audit your models, you’ll find this tool can be helpful. It shows how the queries link together and flow from one to the other.