I have a power query table auto refresh on several pages of an excel worksheet. Ive added column A a formula to create some new numbers. The problem is some sheets the column A auto updates upon refresh and some sheets it does not and column A is still be blank. This wikiHow teaches you how to use the Power Query feature in Excel 2016 on a Windows and Mac computer. Power Query is an Excel function that allows you to import data from a variety of sources (Excel tables, CSV files, online database, etc.), and easily reconfigure the data into a pivot table in a spreadsheet.

Provides an introduction to Power Query’s “M” language; Prerequisites: This is an intermediate course and users should already be comfortable with Excel formulas to get the most from this course. Participants should be familiar with functions & concepts such as VLOOKUP and SUMIFS. The focus is on Excel 2016 and Power BI Desktop but the. I have recently installed Office 2016 home and student. My problem is that I can't get the Power Query tab into the Excel ribbon. I have downloaded and installed the most recent version of Power Query for Excel but that has not automatically enabled it.

At work this week, I needed to compare two tables to see if the they had similar data. The problem was they were aggregated at different levels with different dimensions and some data in table A was not in table B and some data in table B was not in table A. I needed to find and quantify these differences as well as locate the missing data in each table. I was dealing with ad units, orders, key values, impressions and revenue from the world of online advertising and dealing with 50,000 or so rows of data, but for this post we will look at a more simplified example.

Power Query is included with Excel 2016 (Office 365). It has been renamed and is now on the Data tab of the Ribbon in the Get & Transform section. This means there is nothing to install. If you are using Excel 2016, go to the Data tab on the ribbon and press the New Query button to create a query and open the Power Query editor.

In this example we have table A and table B.

• Table A contains data for clothing purchases by customer and product along with the quantity purchased and the total amount paid. This table has two dimensions of customer and product, and two metrics of quantity purchased and amount paid.
• Table B contains data for the same clothing purchases by customer and product with an extra dimension of description and also includes quantity purchased and the total amount paid. This table has three dimensions of customer and product, and two metrics of quantity purchased and amount paid.

We want to compare these two sets of data and find out where the differences are and quantify these differences. If we take a look at the data we can see some differences.

• Table A contains data for Alex but table B is missing Alex
• Geoff has 2 sweaters in table A but has 3 sweaters in table B

There are several other differences in the tables, but spotting them manually will be hard and won’t scale when your tables have more data. Get & Transform will allow us aggregate these tables to the same level of granularity and join the aggregated data by their common dimensions to easily find the differences.

Get & Transform was previously called Power Query in Excel 2010 and 2013, and you will need to install is as an add-in. Find out how to install Power Query here. If you’re running Excel 2016 then it’s already installed and can be found in the Data tab of the ribbon.

Aggregating The Tables

First we will need to aggregate the data to the customer and product level so that we can compare the two tables.

If you look at table A, you will notice that Bob and Jane have rows of data that will need to be aggregated. We ideally want only 1 row of data for Bob and Sweaters and 1 row of data for Jane and T-Shirt.

TIP: I always find it’s a good idea to use Excel Tables with your data. This way, your queries can reference a table name instead of a range. When you add data you won’t need to update the range in your queries as they will reference the name. The data sets in this example have already been turned into tables named Table_A and Table_B, but you can read about how to make a table here.

First let’s select our table and make a query.

1. Select a cell in table A or select the whole table.
2. Go to the Data tab in the ribbon.
3. Press the From Table / Range button in the Get & Transform section.

This will open up the Query Editor.

Then select a Group By transformation.

1. Go to the Transform tab.
2. Press the Group By button.

Create your Group By query.

1. Select Advanced to create a Group By query which groups by more than one dimension.
2. Select Customer first and then Product second. You will need to use the Add grouping button to add a second dimension.
3. Add a descriptive column name, select Sum as the Operation for both the Quantity and Amount columns. You will need to use the Add aggregation button to add the second metric.

Now save the query.

1. Go to the Home tab in the query editor.
2. Press the Close & Load button.
3. Select Close & Load To.

From the Import Data menu select Only Create Connection. We could load this to another table by selecting Table if we want to see this intermediary step in our spreadsheet, but it’s not necessary. We can also select where to load the table to if we do select Table. Press the OK button to finish.

You should now see the Queries & Connections window pane docked to the right of your spreadsheet and it will contain our new Table_A query.

We can repeat the same process to create a Group By query for Table_B with the exact same groupings.

You should now see two Connection only queries in the Queries & Connections window pane for Table_A and Table_B.

Join Queries With Merge

Now we will combine our queries.

1. Go to the Data tab.
2. Press the Get Data button from the Get & Transform Data section.
3. Choose Combine Queries then Merge from the menu.

Now we can setup our merge query.

1. Select Table_A for the first query.
2. Select Table_B for the second query.
3. Select Full Outer (all rows from both) for the Join Kind. This will mean all rows in Table_A and all rows in Table_B will be shown in the resulting table.
4. Now we can select which columns our merge query will join on. Hold Ctrl then click on the Customer column and then the Product column. You should see a small 1 next to Customer and a small 2 next to Product.
5. Hold Ctrl then click on the Customer column and then the Product column. We should again see a small 1 next to Customer and a small 2 next to Product.
6. Press the OK button.

In the editor we will see our Table_A Group By query along with a Table_B column. We will need to expand this column to show the data in our Table_B Group By query.

1. Right click on the expand icon in the right side of the Table_B column.
2. Select Expand.
3. Press the OK button.

Go to the Home tab and press the Close & Load button to create a table of the results in a new sheet.

Compare Data

It’s now easy to compare the data in table A and B and see where the differences are.

When it comes to Microsoft’s business intelligence products, understanding the basic terms can be complicated – especially if you are just diving into data & analytics. So let’s answer the questions which you had been afraid to ask such as:

1. What are the differences between Excel and Power BI?
2. When is it best to use Power BI and when should you rather use Excel?
3. What are the differences between Power Query, Get and Transform, and Query Editor?

***If you find this blog helpful, you can subscribe to our newsletter. If you need help with your data processing, shoot us an email.***

The basic question. What are the differences between Excel and Power BI?

Microsoft Excel

Excel has been with us for a while – since 1985. It’s the tabular processor which everyone is using. Over the years it’s been updated many times and the current version (today is 2018-10-28) look like this:

Throughout this article, I am using sample crime dataset from FBI, you can get it here.

Excel can serve you with many things:

1. You can do ad hoc calculations across random set of cells and tables
2. You can create pivot tables which can replace some of your basic database needs
3. You can create charts
4. You can install many extensions – some provided by Microsoft (e. g. Solver), some provided by 3rd parties (see some examples here)

Excel is an all-rounder. in fact it can be the only tool you will ever need to perform your data analysis when your datasets are fairly small.

Microsoft Power BI

Okay, now things get little confusing. When you say “Power BI”, it can be mean several things so let’s deconstruct all the meanings. According to Microsoft, “Power BI is a suite of business analytics tools that deliver insights throughout your organization.”. Notice the word “suite”. Yes, it’s not just 1 product. So what is the suit made of?

Microsoft Power BI for Desktop

Power BI for Desktop is an application running on Windows machines. Sorry Mac users. No version for Macs yet. Power BI for Desktop is a tool which allows you to design your dashboards based on your database-like data structure = many rows and many columns. Simply said, imagine Power BI for Desktop as Excel which does not have any cells and which only allows you to add visual elements such as charts, various indicators or calculated metrics when it comes to analyzing the metrics.

Power BI for Desktop looks like this:

I told you – no cells in Power BI for Deskop! It’s a canvas into which you add visualization elements. When you know some basics, you can create something like this:

I am using simple table and chart visualization element. Again, I am using the same dataset as for the Excel example.

Power BI for Deskop is the app where you design your dashboards which can either sit in your PC or which can be uploaded to Power BI online service where it can refresh automatically if certain conditions are met. As I said, the data coming into Power BI Desktop must be at least slightly structured, forget doing ad hoc calculations somewhere on the side, it’s very difficult to make e. g. sums between 2 cells on different rows. The data just needs to be rows and columns and you treat it like database.

Power BI online service

When you are done with designing your dashboard, you probably want to have it refreshed automatically on regular basis. That’s when Power BI online service comes in. Power BI online service is a server which can do the automated refreshes for you. I am saying “can” on purpose – your data needs to be accessible online via one of the connectors which Power BI supports. This is a topic for separate article which is coming soon.

When you log into Power BI online service in your browser, you will see:

Then you locate the report which we just created:

Yes, it does look very similar to what you see in the application for your Windows Desktop, the difference is that this report now lives online and could be refreshed automatically if your data is reachable online. Since I am using a very simple dataset downloaded from FBI’s website to my desktop, my dataset is not refreshable via the online service by default.

It’s a simple question but the answer is more difficult. Excel in its pure form is good for ad hoc analysis and calculations when you don’t necessarily have database like structure. Assume this dumb example:

18 is the sum of the remaining numbers on the sheet. In Excel, it will take you 30 seconds to get to the sum of 18. In Power BI for Desktop, it’s nearly impossible to do it. So Power BI must suck, right? Not necessarily. Assume you have several million rows of crime related data from FBI in CSV file:

Okay, my screen is showing just 20 rows, but assume the input file has 20,000,000 rows. Will you able to process such data in pure Excel? No. problem number 1 is that Excel can carry only 1,048,576 rows on one sheet. Problem number 2 is that Excel gets very slow when dealing with hundreds of thousands rows because it caches everything into RAM unlike Power BI which “processes” every step but stays idle when doing nothing.

Get And Transform Excel 2016

So when you have thousand of rows of structured data, go for Power BI for Desktop. When you need to do some random calculations, go for Excel.

Access Power Query In Excel 2016

There is “one more thing”. Microsoft has decided to make matters worse and use 3 different names for pretty much the same thing. The “same thing” is the set of features both in Excel and in Power BI which allows you to clean up & transform your messy data. Imagine it as a “standalone” module in Excel and in Power BI which opens in a separate window and runs on the top of Excel and Power BI:

1. Power Query = Get and Transform – that’s how those features are named in Excel. It used to be called “Power Query” but it got renamed to “Get and Transform” in Excel 2016.
2. Query Editor – that’s how those features are named in Power BI.

The set of features allows you to perform ETL processes (extract-transform-load) such as:

1. Filtering only for specific rows
2. Getting rid of headers
3. Getting rid of bottom total rows
4. Unpivoting regular tables in order to make them look like a database tables
5. Adding custom columns based on IF conditions
6. Joining tables together based on the common keys
7. Appending multiple tables together
8. And much more…

Power Query In Excel 2016

After you are done with extractions and transformations, you can use the processed data as input for your pivot tables in Excel or as input for your visualizations in Power BI. In fact, when showing the FBI example earlier in the article, I had to get rid of the 3 top rows, and get rid of the notes at the bottom before I could load the data for visualization. That clean up process was done in Query Editor in Power BI.

Screenshots in Excel:

Then when you hit “Launch Query Editor”, a new window running on the top of Excel will open:

Now moving onto Power BI for desktop:

Hit “Edit Queries” and you will see:

When you compare the buttons in the query editor in Excel and in Power BI, you will notice they are almost exactly the same 🙂

Knowing how to use the query editor in Excel or in Power BI brings your analytical skills to whole another level – you will no longer have to suffer when trying to refresh messy data. You do your query once as stepped process and then you just keep refreshing it next time.

In this article, I mentioned “pure Excel” few times – that’s the Excel everybody knows with all its limits (like 1,048,576 rwos). Pair it with Query Editor all the limits are gone. You can now load millions of rows into Excel and use them as source for your pivot tables.

***If you find this blog helpful, you can subscribe to our newsletter. If you need help with your data processing, shoot us an email.***

Learn Power Query In Excel 2016

We hope we’ve helped you understand the basic terms around Microsoft’s analytical suite. More articles to follow!