Introduction to Power Query Part 2

Author: David H. Ringstrom

In the second part of this two-part series author and Excel expert David H. Ringstrom, CPA takes you deeper into the possibilities of Power Query. The course leads off with extracting data from text files and then presenting the results in a PivotTable format. You'll also see how to relate multiple data sources together by merging files and lists. If time allows additional topics include linking to profit and loss reports and separating SEDOLS from amidst a column of text.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) during the course as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: April 2023

Designed For
Professionals that import and manipulate any sort of reports in Excel or that need to transform data from one format to another

Topics Covered

• Filtering a cleaned-up accounts receivable aging report to display only overdue amounts
• Understanding how you can work backwards through applied steps in Power Query to visually step through data transformations
• Creating a self-updating list of worksheets in any workbook with Power Query
• Navigating purposefully through worksheets by way of clickable hyperlinks
• Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more
• Managing data security prompts that may appear when you link external data into Excel spreadsheets
• Introducing the Power Query feature in Excel
• Appending data from two or more worksheets into a self-updating consolidated list with Power Query
• Configuring Power Query queries to update automatically in the most efficient manner possible
• Transforming reports that are dozens or more columns wide into filterable rows by way of the Unpivot command in Power Query
• Extracting data from PDF files with Power Query in Microsoft 365
• Extracting text from the middle of a cell that has non-breaking spaces around it by way of Power Query (non-breaking spaces often appear in reports copied or generated from web pages)

Learning Objectives

• Identify which menu the Append Query command appears on within Power Query
• Identify which command enables you to send data from the Power Query editor to a pivottable
• Identify which Power Query Editor command enables you to import data

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience working in Microsoft Excel

Advance Preparation
None