All You Need to Know About

Learn Power Query, Power Pivot & Power BI

Turn data into opportunity with Microsoft’s Business Intelligence & data visualization tools..

Power BI in Excel has a lot of powerful components working together: Power Query, Power Pivot and Power View.

Excel Power Query is one of the most powerful new features within Microsoft Excel and the easiest to learn.

It was first introduced as an Excel add-in in 2013 and was then made available in Excel 2010.  In Excel 2016 it was renamed to Get & Transform and was available (without using an add-in) under the DATA tab in the ribbon menu.  In Excel 2019, it will be renamed to Power Query once again!

You can use Power Query to clean & transform your data that you download from your ERP or accounting system and display it in a report for Excel to work with.

The best thing is that you can reapply the same transformation steps in just ONE single click, saving you HOURS! So next week when you get the same report with updated data, a simple REFRESH will transform your data once again!

Power Pivot on the other hand gives you the power to work with large sets of data.  In a nutshell, it allows you to use multiple data sources. Then you could import, merge and perform analysis on the resulting data. The beautiful thing with Power Pivot is it allows you to work on Big Data with no limitations.

Imagine getting data from multiple sources like SQL Server, Oracle, XML, Excel, Microsoft Access then build a Data Model from it. Then you can analyze these all into one awesome Pivot Table!

Read on to find our best Power BI Tutorials to get yourself up to speed!

Consolidate Multiple Excel Sheets Using Power Query Append

One of the top questions I get from blog readers is if there is a way to easily consolidate multiple Excel worksheets into one.

With Power Query the answer is a big YES!

If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates, then we can easily consolidate all the worksheets into one.

The crucial part here is it has to be in the same format so that we can append them together.

STEP 1: Make sure that each worksheet’s data is in an Excel Table by clicking in the data and pressing CTRL+T

excel table

STEP 2: Click in each of the worksheets data that you want to consolidate and select:

Power Query > From Table

from table

STEP 3: This will open up the Query Editor and all you have to do here is press Close & Load.  

Make sure to do Steps 2 & 3 for each worksheet you want to consolidate. This will include load all our worksheets into Power Query.

close & load

STEP 4: Select Power Query > Append 

Append

STEP 5: Choose the Three or more tables option

three or more table

STEP 6: Add the tables to append from the Available Tables (from the left) to the Tables to Append (to the right) by selecting and pressing the Add button.

You can also organize the order that you want your consolidated table to appear by moving the Tables up or down.

So your merged table will be a combination of the tables listed on the right.

Press the OK button!

append2

STEP 7: This will open up the Query Editor once again.  Choose Close & Load.

close & load2

STEP 8: This will open up a brand new worksheet which will consolidate all the worksheets into one big Table.

No more manual copy and pasting! If the underlying tables get their data updated, this Power Query result will retrieve these changes as well when you do a refresh!

consolidated table

STEP 9: From this consolidated worksheet you can Insert a Pivot Table and do your analysis:

pivot table

Linking Excel Tables in Power Pivot

When you have multiple tables, Power Pivot can help you link them together. After linking them together you can then create a Pivot Table that will give you a single view of data.

What we will focus on is a simple example of two Excel Tables: a Name Table and a Sales Table.

Linking Excel Tables 01

What we want to know is how much each Employee made in Total Sales. 

You can see that each employee is uniquely identified by the ID number, which is also used in the Sales table. This is a crucial identifier for us to relate the two tables together.

STEP 1: Select your first table. Go to Insert > Table. Click OK.

Linking Excel Tables 02

STEP 2: Go to Design > Table Name  and give your Table a descriptive name. In our example, we will name it Names

Linking Excel Tables 03

STEP 3: Select your second table. Go to Insert > Table. Click OK.

Linking Excel Tables 04

STEP 4: Go to Design > Table Name  and give your Table a descriptive name. In our example, we will name it Sales

Linking Excel Tables 05

STEP 5: Select your first table. Go to Power Pivot > Add to Data Model. This will import your new Table into the Power Pivot Window.

Linking Excel Tables 06

STEP 6: Select your second table. Go to Power Pivot > Add to Data Model. This will import your new Table into the Power Pivot Window.

Linking Excel Tables 07

STEP 7: This will open the Power Pivot Window. Your two Tables should already be loaded there.

Linking Excel Tables 08

Linking Excel Tables 09

STEP 8: Go to Design > Create Relationship.

What will define our relationship? It will be the ID column linking these two tables together.

Linking Excel Tables 10

STEP 9: Ensure for Table 1, you set Names = ID and for Table 2, you set it to Sales = ID.

This will set the relationship and your Sales table will be able to see the values in the Names table.

Linking Excel Tables 11

 

STEP 10: With this, our setup is complete.  Now it’s time to create a Pivot Table to do our analysis!

You will be excited to see how our Pivot Table will be able to use the data from two tables simulatenously!

Within the PowerPivot Window, go to Home > PivotTable

Select New/Existing Worksheet and press OK

Linking Excel Tables 12

STEP 11: This will create a new Pivot Table within your Excel worksheet.

We will get information from both tables! Without any additional setup needed because we have put the relationship in place.

In the ROWS area put in the Name field from the Names Table,  in the VALUES area you need to put in the Sales Amount field from the Sales Table:

Linking Excel Tables 13

STEP 12: We now have the Names and the Total Sales Amount all in one Pivot Table.

We were able to link and consolidate two Excel Tables together with no need for VLOOKUP or helper columns…thanks to Power Pivot!

Linking Excel Tables 14

Here are our favorite Power BI Tutorials that you can continue to watch!

Top 10 Tutorials

  1. 50 Things You Can Do With Excel Power Query– I have compiled an interactive tutorial on the 50 different things you can do with Excel Power Query.Read more
  2. Consolidate Multiple Excel Workbooks Using Power Query– Luckily with Power Query this consolidation task can be done in a couple of minutes! That’s right, only a couple of minutes.Read more
  3. Consolidate Multiple Excel Sheets Using Power Query Append– If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates (e.g. January Sales List, February Sales List, March Sales List etc), then we can easily consolidate all the worksheets into one.Read more
  4. Replicating Excel’s LEFT Function with M in Power Query– If we want to use the LEFT Excel Function, it is not supported in the M programming language. But I have found a way for us to replicate the LEFT Function in M!Read more
  5. Enabling Power Pivot in Excel 2016– The good thing with Power Pivot is it already comes with your Excel 2016, we need to enable it to start using Power Pivot.Read more
  6. Replicating Excel’s RIGHT Function with M in Power Query– If we want to use the RIGHT Excel Function, it is not supported in the M programming language. But I have found a way for us to replicate the RIGHT Function in M!Read more
  7. Remove Rows Using Power Query– One of the most common steps I do, is to clean my data and remove rows, and Power Query gives you a lot of options to do so!Read more
  8. Remove Duplicates Using Power Query or Get & Transform– One of the steps it allows you to take is to remove duplicates easily. This removes the human error whenever you try to delete your duplicate data manually!Read more
  9. Linking Excel Tables in Power Pivot– When you have multiple tables, Power Pivot can help you link them together. After linking them together you can then create a Pivot Table that will give you a single view of data.Read more
  10. How To Install Power Query in Excel 2010– Power Query is a new add-in that was introduced in Excel 2013 by Microsoft and was that popular that they made it backward compatible with Excel 2010.Read more

Read More

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]