Learn to use Power Query, the Excel Data Model, and PivotTables to get it all set up fast. Discover how to import and create a connection to the data and refresh it with one click using Power Query. Leila also shows how to use Power Query to clean data and set up calculation logic. Power Query is built in to Windows versions of Office 365, Excel 2016, Excel 2019 and is available as a free download in Windows versions of Excel 2010 and Excel 2013. The tool is designed to extract, transform, and load data into Excel from a variety of sources.
November 11, 2020 - by Bill Jelen
Power Query is built in to Windows versions of Office 365, Excel 2016, Excel 2019 and is available as a free download in Windows versions of Excel 2010 and Excel 2013. The tool is designed to extract, transform, and load data into Excel from a variety of sources. The best part: Power Query remembers your steps and will play them back when you want to refresh the data. This means you can clean data on Day 1 in 80% of the normal time, and you can clean data on Days 2 through 400 by simply clicking Refresh.
I say this about a lot of new Excel features, but this really is the best feature to hit Excel in 20 years.
I tell a story in my live seminars about how Power Query was invented as a crutch for SQL Server Analysis Services customers who were forced to use Excel in order to access Power Pivot. But Power Query kept getting better, and every person using Excel should be taking the time to learn Power Query.
Get Power Query
You may already have Power Query. It is in the Get & Transform group on the Data tab.
But if you are in Excel 2010 or Excel 2013, go to the Internet and search for Download Power Query. Your Power Query commands will appear on a dedicated Power Query tab in the Ribbon.
Clean Data the First Time in Power Query
To give you an example of some of the awesomeness of Power Query, say that you get the file shown below every day. Column A is not filled in. Quarters are going across instead of down the page.
To start, save that workbook to your hard drive. Put it in a predictable place with a name that you will use for that file every day.
In Excel, select Get Data, From File, From Workbook.
Browse to the workbook. In the Preview pane, click on Sheet1. Instead of clicking Load, click Edit. You now see the workbook in a slightly different grid—the Power Query grid.
Now you need to fix all the blank cells in column A. If you were to do this in the Excel user interface, the unwieldy command sequence is Home, Find & Select, Go To Special, Blanks, Equals, Up Arrow, Ctrl+Enter.
In Power Query, select Transform, Fill, Down.
All of the null values are replaced with the value from above. With Power Query, it takes three clicks instead of seven.
Next problem: The quarters are going across instead of down. In Excel, you can fix this with a Multiple Consolidation Range pivot table. This requires 12 steps and 23+ clicks.
In Power Query select the two columns that are not quarters. Open the Unpivot Columns dropdown on the Transform tab and choose Unpivot Other Columns, as shown below.
Right-click on the newly created Attribute column and rename it Quarter instead of Attribute. Twenty-plus clicks in Excel becomes five clicks in Power Query.
Now, to be fair, not every cleaning step is shorter in Power Query than in Excel. Removing a column still means right-clicking a column and choosing Remove Column. But to be honest, the story here is not about the time savings on Day 1.
But Wait: Power Query Remembers All of Your Steps
Look on the right side of the Power Query window. There is a list called Applied Steps. It is an instant audit trail of all of your steps. Click any gear icon to change your choices in that step and have the changes cascade through the future steps. Click on any step for a view of how the data looked before that step.
When you are done cleaning the data, click Close & Load as shown below.
Tip
If your data is more than 1,048,576 rows, you can use the Close & Load dropdown to load the data directly to the Power Pivot Data Model, which can accommodate 995 million rows if you have enough memory installed on the machine.
In a few seconds, your transformed data appears in Excel. Awesome.
The Payoff: Clean Data Tomorrow With One Click
But again, the Power Query story is not about the time savings on Day 1. When you select the data returned by Power Query, a Queries & Connections panel appears on the right side of Excel, and on it is a Refresh button. (We need an Edit button here, but because there isn't one, you have to right-click the original query to view or make changes to the original query).
It is fun to clean data on Day 1. I love doing something new. But when my manager sees the resulting report and says “Beautiful. Can you do this every day?” I quickly grow to hate the tedium of cleaning the same data set every day.
So, to demonstrate Day 400 of cleaning the data, I have completely changed the original file. New products, new customers, smaller numbers, more rows, as shown below. I save this new version of the file in the same path and with the same filename as the original file.
If I open the query workbook and click Refresh, in a few seconds, Power Query reports 92 rows instead of 68 rows.
Cleaning the data on Day 2, Day 3, Day, 4,...Day 400,...Day Infinity now takes two clicks.
This one example only scratches the surface of Power Query. If you spend two hours with the book, M is for (Data) Monkey by Ken Puls and Miguel Escobar, you will learn about other features, such as these:
- Combining all Excel or CSV files from a folder into a single Excel grid
- Converting a cell with Apple;Banana;Cherry;Dill;Eggplant to five rows in Excel
- Doing a VLOOKUP to a lookup workbook as you are bringing data into Power Query
- Making a single query into a function that can be applied to every row in Excel
For a complete description of Power Query, check out M Is for (Data) Monkey by Ken Puls and Miguel Escobar. By late 2019, the retitled second edition, Master Your Data, will be available.
Thanks to Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser, and Colin Michael for nominating Power Query.
Title Photo: pan xiaozhen at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.
One of the interesting additions to the Power Query Preview December update is the ability to import data from Microsoft Exchange. In this blog post, Miguel Llopis (a Program Manager in the Power Query team) will walk us through the capabilities exposed by this new feature and show how you can easily do analytics on top of your Exchange account.
Connecting to Exchange using Power Query
If you have installed the latest Power Query Preview update you will be able to connect to Microsoft Exchange via the “From Other Sources” menu in the Power Query ribbon.
After selecting this option, you will be prompted for your Exchange account credentials. Once you provide credentials the Navigator side pane will show the list of items available in your Exchange account, including Mail, Calendar, People, Tasks and Meeting Requests. You can use the Navigator to preview and select one or more items from this list. The Navigator will then let you load these items directly into the workbook or apply some filters and transformations before loading.
In this case, we will select “Mail” and click “Edit”, so we can apply filters and transformations to our query before downloading the Mail data from Exchange. The Query Editor will show us a preview of the Mail items in my account, where we can apply any table transformations available in Power Query just as we do with data coming from other sources like databases or files.
Analyzing emails sent directly “to me” per day
To showcase some of the possible analytics that we could build, we will create a query that returns the number of emails sent directly to me by day.
You can see how the first column in the table contains the Folder Path information for each Mail item. In this case, my Exchange account has a set of rules and it sends the items that contain my email address in the “To” line to a certain folder (“ToMe”). We can filter this column to only include items in this folder, by using the Filter menu in the column header. You will notice that, because I have too many items in the Inbox folder, the filter values list only displays “Inbox” initially, since the Query Editor only uses a sample of the data for preview and transformation purposes, but there is a way for us to load up to the top 1,000 distinct values in this column by using the “Load more” link at the bottom of the filter menu.
After loading more items, I can search for the “ToMe” folder and apply the desired filter.
Note that even if you don’t have custom rules and a folder structure to organize your mail, you can also achieve the goal of filtering down to only include emails with a certain recipient in the To line by expanding the “ToRecipients” column in this table and filtering by email address.
Now that we have filtered down to only the items that we want to analyze, let’s apply some extra transformations so we are able to group these items by received date. Notice that the table contains a “DateTimeReceived” column that we can use. In this case, since we want to group just by received date and not date/time, we will need to change the type of the “DateTimeReceived” column to Date. In order to do that, we can select the column and use the Data Type menu in the ribbon to set the column type to Date.
Let’s also rename this column to “Date Received”.
To simplify this table and filter out unnecessary columns for our analysis, we can remove all columns in the table except for the “Subject” and “Date Received” columns, and change the Load Settings at the bottom of the Query Settings pane so we load the resulting data directly into the Data Model and not to the worksheet.
Now we can click “Apply and Close” and the query will start evaluating. This evaluation may take some time, as in my case it will download over 35,000 rows of Exchange mail data.
Creating a Power View report
Once the query has finished downloading, we can use Power View to build a line chart that displays the count of items per date.
It doesn’t come as a surprise, but we can see how the volume of my incoming emails per day during the last year decreased during the holidays, such as Christmas and New Year, or during some other periods when I took time off work.
Sharing this query with others
In addition to visualizing this report, I could also share the query with other users using the Power BI for Office 365 Preview. I can share the query with everyone in my organization, but given that I don’t want to include a preview of the query (which contains email subjects), I can disable the preview as part of my sharing settings.
After sharing the query, it will be easily discoverable for other users in my organization via Online Search, and they will be able to run the same analysis using their own Exchange account credentials.
Power Query Office 365 Proplus
Additional resources: