First steps with Power Query for Microsoft Excel

Resources for course, "First steps with Power Query for Microsoft Excel".

Power Query is a Microsoft extract, transform, and load (ETL) technology that enables users to access data from structured and unstructured sources, reshape it, and load it into Excel workbooks.

Analysts, researchers, and project managers want to build repeatable data cleaning processes on large datasets. Rather than write complex Excel formulas or programs in Visual Basic for Applications (VBA), these users can conduct their work in Power Query with no coding required.

The focus of this course will be using Power Query to transform tabular data sources into tables that are ready for data analysis. By the end of the course, users will be able to pivot, filter, and merge data so that it’s ready for analysis in Excel.

What you'll learn-and how you can apply it

By the end of this live, hands-on, online course, you’ll understand:

• How Power Query serves as an ETL tool for Microsoft Excel
• What makes data “tidy,” and why tidiness is desirable for data analysis
• The role of the M programming language in Power Query

And you’ll be able to:

• Load data from Excel worksheets, workbooks and CSV files
• Perform common data wrangling tasks such as sorting, filtering, and aggregation
• Combine data from multiple sources using joins and appends

This training course is for you because...

• You're an analyst responsible for collecting, analyzing, and interpreting data for business insights
• You frequently gather data from multiple sources to build reports in Excel
• You want to build repeatable data cleaning procedures with no code

Prerequisites

• You should be comfortable with basic tasks and functions in Excel, including sorting and filtering, and the use of IF statements. Ideally, you've also used conditional aggregates like SUMIF() and COUNTIF() as well as PivotTables and VLOOKUP().

Recommended preparation:

• Download and install Power Query for Excel. Depending on your version of Excel, the steps will be different. See Microsoft’s official compatibility guide. Please note that on a Mac, Power Query is only available on Office 365. If you need a refresher on Excel basics, consider:
• Taking - Foundations of Microsoft Excel (live online training course with Dawn Griffiths), or
• Watching the “Excel Tables” and “Pivot Tables” sections of Big Data Analytics with Excel (video course)
• Read Chapters 8 “Introducing Power Query” and 9 “Power Query Connection Types” in Excel Power Pivot and Power Query For Dummies (book)

Recommended follow-up:

• Read Collect, Combine, and Transform Data Using Power Query in Excel and Power BI (book)

Schedule

The timeframes are only estimates and may vary according to how the class is progressing Power Query as Excel’s ETL tool (25 minutes)

• Presentation: Why ETL for business analytics?
  • Overview of what is meant by “extract, transform and load” processes, with applications in business analytics and reporting
• Presentation: What would we do without Power Query?
  • Tour of the alternatives to Power Query: complex formulas, VBA, SQL, and more
• Presentation: Power Query and “Modern Excel”
  • Overview of Microsoft’s “Power Platform” as applied to Excel: Power Pivot, Power Query, M, DAX.
• Exercise: Assess how to clean a messy dataset using known tools. Discuss what makes that dataset messy.
• Q&A

What Makes Data “Tidy?” Why Does it Matter? (25 minutes)

• Presentation: Spotting messy data in the wild
  • Using the principles of “tidy data,” explicitly state how to reshape a dataset for ease of analysis
• Exercise: Getting to tidy: what needs to change with these datasets?
• Presentation: First steps to tidy in Power Query
  • Load a first data source into Power Query, inspect it with data profiling, and begin the data cleaning process
• Q&A
• Break (5 minutes)

Transforming Rows in Power Query (25 minutes)

• Presentation: Sorting, removing duplicates, filtering, filling and grouping a table
• Exercise: Drills on transforming rows
• Q&A

Transforming Columns in Power Query, Part I (30 minutes)

• Presentation: Changing data types, splitting columns, and re-formatting text
• Exercise: Drills on cleaning, delimiting and transforming columns of a table
• Presentation: Managing & loading queries
  • Monitor and edit the steps of a query, load the results, and refresh the query
• Exercise: Drills
• Q&A
• Break (5 minutes)

Transforming Columns in Power Query, Part II (30 minutes)

• Presentation: Concatenating columns, creating calculated fields, and un-pivoting tables
• Exercise: Drills on re-shaping and adding calculated fields to a table
• Presentation: Appending several tables
• Q&A

VLOOKUP(), meet JOIN (30 minutes)

• Presentation: Illustrate the differences between left and inner joins
• Presentation: Conducting joins in Power Query
• Exercise: Drills on joining two tables together
• Q&A