Power Query

Name: Power Query
Rating: 4.2 (59 reviews)

Gather, Cleanse & Transform Your Data

Created byEarn And Excel

Last updated 8/2020

English

English [Auto],

What you'll learn

Complete Mastery of Power Query
Introduction to getting data from various sources and cleaning it up
Rapidly transpose, pivot and un-pivot your data to make it useful
How you can quickly combine multiple data sets by appending and joining
Speed up your data queries with duplication and tweaking parameters
Troubleshoot your queries and set up auto refreshing of your data

Course content

5 sections • 40 lectures • 5h 36m total length

1.1 Introduction to Power Query3:15
For this course you will find a zip file for each module.
The zip file contains a PDF with steps to help you walk through the lesson after watching the video. A few of the lessons have no PDF as they are informational only.
The zip file also contains any Excel sheets you need to replicate the actions in the module.
1.2 Getting Familiar with the Power Query Editor9:23
1.3 First Data Clean Up for a Pivot Table7:06
Power query guide demonstrates cleaning data to report sales and profit in a pivot table, adding a profit column, creating a year field, and refining product names by splitting abbreviations.
1.4 Tweaking our Query Until the Data is Correct9:25
Turn a query output into a pivot table, then clean data by trimming spaces, replacing values, and adjusting case before reloading the pivot.
1.5 Rounding Data4:28
1.6 Altering Data Types2:19
Power Query 1.6 alters data types by exploring text versus numeric formatting, showing how changing the amount from numeric to text breaks the pivot table and yields zero sums.
1.7 Filtering Out Data Before We Load It1:09
Filter data by region to load only United States sales in Power Query before load, so the dataset drops from over 52,000 rows to 26,000 when non-US data is excluded.
1.8 Advanced Calculated Columns8:59
Power Query instructors demonstrate calculating profit as amount minus total cost, ensure correct column order, then derive profit percentage and create bands with conditional columns for pivot tables.
1.9 Grouping Columns7:03
Power Query groups by product and sums amounts and profits to reduce data sent to pivot tables, dramatically decreasing rows from thousands to a few dozen for faster analysis.
1.10 Using Columns From Examples8:39
Power query shows how to add a new column from examples by entering samples, such as abbreviating 'combat flight simulator' to 'combat flight SIM', while warning about overloading transformations.

2.1 Introduction to Transpose, Pivot and Un-Pivot9:43
2.2 Pre-preparing Messy Data7:30
2.3 Appropriating Our Data with Transpose14:32
2.4 Un-Pivoting Data6:24
Learn to pivot and unpivot data in Power Query, converting color and quantity into a single color column with corresponding values, and revert to the original table.
2.5 Pivoting Data9:24
Import IMF CPI data, promote headers, remove unused columns, filter indicators, and pivot years into a single column with separate measures for indicators.
2.6 Creating a Pivot Chart2:34

3.1 Appending Data8:39
Learn to combine data from two tables in Power Query by appending and merging queries, then remove duplicates and load the unique results back to the worksheet.
3.2 Introduction to Merging Data1:32
3.3 Left Outer Join6:37
3.4 Right Outer Join4:19
Demonstrate right outer join in Power Query by comparing it to left join, showing all rows from the second table with matching first-table data and nulls for nonmatches.
3.5 Full Outer Join6:18
Explore full outer join behavior in Power Query, returning all rows from both tables, expanding and aligning columns, and using conditional columns to merge rep and region data.
3.6 Inner Join3:29
3.7 Left Anti Join2:18
Perform a left anti join to return rows from the first table that have no matching rows in the second table, showing Bob and Dave as unmatched records.
3.8 Right Anti Join2:57
Explore the right anti join in power query, returning only rows from the second table not found in the first, with left table columns first, then right data.
3.9 More Appending with Bigger Data Sets13:29
Fetch multiple web tables with Power Query, then append and merge them into one dataset of albums sold over 20 million, handling headers and duplicates.
3.10 Going Deeper with Merging Data23:12
Merge three data sources in Power Query to analyze artist album sales across ranges (20–29m, 30–39m, 40m+), using inner and outer joins, grouping, and pivoting to count albums per artist.

4.1 Duplicating Queries6:43
Explore duplication and referencing of queries in Power Query to safely test changes on a duplicate without altering the original, and reuse duplicated outputs for pivot tables.
4.2 Speeding Up Query Editing with Duplication8:14
Learn to speed up Power Query editing by splitting into a base data query for all countries and a USA-specific query using extract previous, avoiding duplicate edits and using connections.
4.3 Filtering Duplicated Queries4:16
Duplicate a query to create a United Kingdom version of a United States pivot table, edit filters and fields without altering the original, and produce separate UK and USA outputs.
4.4 Working with References4:34
Explore how to build and manage Power Query references, duplicating queries for country filters USA, UK vs. using base-data references for Australia, and understand the impact of base data changes.
4.5 Parameters25:03

5.1 Functions14:58
Explore how to create and apply custom functions in Power Query, calculating discounts from amount, quantity, and unit price, with testing and integration into queries.
5.2 Dealing with Errors13:11
Power Query teaches how to identify and fix data errors, create error-focused queries, and use techniques like keep errors, remove errors, and replace errors with null for ongoing data validation.
5.3 Steps Part 1: Troubleshooting Queries17:20
Safely edit queries by backing up workbooks, duplicating queries, and managing steps to prevent failures. Use insert, delete, rename, and move operations, and rely on error indicators to diagnose issues.
5.4 Steps Part 29:33
Learn to manage large Power Query transformations by giving steps descriptive names and descriptions, documenting splits, and relying on tooltips and automatic reference updates for maintainability.
5.5 Queries and Groups12:54
Rename and describe queries for clarity, group them into meaningful categories like country-specific and test queries, and use properties, peak previews, and a separate parameters group to keep data organized.
5.6 Advanced Query Editor13:37
Explore how Power Query translates user interface actions into M code in the advanced editor, revealing step by step data transformations and the role of source, headers, and merging.
5.7 Query Refresh7:29
Learn to refresh individual or all queries in a Power Query workbook, adjust background refresh and timing, and manage source data updates and refresh order.
5.8 Dependencies7:46
Examine power query dependencies with the dependency tree to see how a base query feeds USA, UK, Australia, and tests, and manage refresh order and data flow.
5.9 Revisiting Combining Queries6:37
Learn a faster way to combine queries in Power Query by merging category queries, counting albums per artist across three sales ranges, and identifying artists with sales in all categories.

Requirements

You need to have an intermediate level of skill with Excel and be comfortable with Data Analysis techniques including Pivot Tables, SUMIF/COUNTIF formulas, sorting and filtering
Access to Excel 219 (365) or 2016, 213, 2010, 2007

Description

If you have been wasting vast amounts of time at work trying to gather, fix and combine your Excel data before you’ve ever done any analysis and reporting, then let our resident Data expert show you how Power Query can transform the way you work!

HOW POWER QUERY CAN PREVENT TIME WASTING

Power Query is the No.1 Excel feature added in the last decade because it can help you get data from various sources, no matter what format it is and clean it all up for you with nothing more than a few clicks! Specifically, there are two ways Power Query saves you vast amounts of time:

If you have reports that are frequently updated, then you just create a ‘Query’ from a source of data then all you need to do is press refresh every time you need to update, and Power Query will carry out all the little steps needed to clean up your data
If you work with messy or unstructured data that consumes a lot of your time to clean up, then Power Query has some functions that instantly fix your data such as unpivoting, appending, joining and transposing to name a few

Power Query’s magic is in saving you from all the usual ways to clean up your Excel data that are far too time-consuming. But it’s not just Excel data you clean up, it’s any data from most Databases, SAP, Salesforce, SharePoint and so on.

HOW THIS COURSE WILL HELP YOU

This video course is taught by our in-house Data expert Ross Hughes who has over 30 years of experience working with Data in senior technical roles within major companies. In a few short hours of self-paced training, Ross will have you going through the full cycle of ‘Getting, Transforming and Refreshing’ your data, after which it will be ready for quick analysis and report.

Ross’ experience is priceless, he shows you how to harness Power Query like few can and having him, teaching you ‘over his shoulder’ is more than worth the price of the course.

WHAT YOU WILL LEARN:

You will learn all you need to know about Power Query to help you deal with difficult data challenges.

· Introduction to getting data from various sources and cleaning it up

· Rapidly transpose, pivot and un-pivot your data to make it useful

· How you can quickly combine multiple data sets by appending and joining

· Speed up your data queries with duplication and tweaking parameters

· Troubleshoot your queries and set up auto-refreshing of your data

Who this course is for:

This is great for Data Analysts, Business Intelligence Analysts, MI Analysts, Project Managers, PMOs and even Accountants (Including Finance Partners & Financial Controllers)who deal with lots of complex data

Power Query

What you'll learn

Explore related topics

Course content

Getting Started With Power Query10 lectures • 1hr 2min

Transpose, Pivot and Un-Pivot6 lectures • 50min

Combining Data From Two or More Data Sets10 lectures • 1hr 13min

Duplication and Parameters5 lectures • 49min

Steps, Groups and Dependencies9 lectures • 1hr 43min

Requirements

Description

Who this course is for: