Note: This article has done its job, and will be retiring soon. To prevent "Page not found" woes, we're removing links we know about. If you've created links to this page, please remove them, and together we'll keep the web connected.
Thanks for taking our Get to know Power Query tutorial. Following are the videos that accompany the learning guide workbook. We suggest viewing the videos in order, but if you just need a refresher, feel free to view them individually.
Note: These videos were produced in Excel for Microsoft 365. If you're not a Microsoft 365 subscriber and are using Excel 2016, we've included instructions if the menu options are different.
1 - Import data from web
Get started with Power Query by downloading historical FIFA world cup soccer standings.
2 - Import from Table/Range
Use Power Query to import data from an existing Excel table. We'll also review the Power Query Editor and its options.
3 - Set up your Header Row
One of the first things you want to do with data is to make sure your header rows make sense. For instance, Product Name is a lot easier to understand than the database header: txt_ProdName.
4 - Data Type conversion
When you work with data from other sources, sometimes the data isn't formatted the way you want. In this case, Order Date is displayed as MM/DD/YYYY HH:MM AM/PM (e.g. 01/01/2019 12:00 AM), but we just want the date, so we'll tell Power Query to only display the Date portion.
5 - Filter to remove unnecessary rows
One of the toughest things to do with data from other sources is narrowing down the data to only show what you want. In this case, we'll show you how to use Filters in the Power Query Editor to remove data you don't want. It's just like using AutoFilter in Excel.
6 - Split data into multiple columns
A lot times you might have to deal with data that's been consolidated into one column, such as First Name | Last Name. For personalization, you might want those to be in separate columns, so we'll show you how to split the data.
7 - Create a Column from Example
In this example, we’ll add several columns to parse out different date types from an Order Date field. This can be helpful if you need to identify the day of the week, or spell out a date such as Monday, January 15.
8 - Create a Conditional Column
Conditional columns let you create calculations based on certain conditions, similar to using an IF statement in Excel. In this video, we’ll create a bonus calculation for all sales over a certain amount.
9 - Merge Queries/Join Tables
In this video, we'll join two distinct tables that are related by Product and Category, then use fields from both tables in a new query that you can send back to Excel. This is similar to using VLOOKUP in Excel to return data from one table or dataset to another.
10 - Review your query’s Applied Steps
When you create transformations in the Power Query Editor, each step is captured for you. In this video, you'll see how to walk through each step to make sure it does what you want. You can edit and rename steps as you go.
11 - Refresh your query when you add data
When you create transformations with Power Query, you can refresh them whenever your data changes. This means you only need to create a query once, and then run it whenever you want. If conditions change, you can also modify your queries as needed.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
