Maximize Excel: Intro to Power Query Part 1
Presented by David H. Ringstrom, CPA
 
Duration: 100 minutes

In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide you through the transformative capabilities of Power Query for enhancing your accounting reports. Discover how to clean up data by eliminating common pitfalls such as blank rows, merged cells, and missing data, ensuring accuracy and clarity in your reports. Learn to change data types within the results grid, manage external data security warnings, and efficiently append data from multiple worksheets into a self-updating consolidated list. David will also demonstrate how to extract data from PDF files using Power Query in Microsoft 365 and explore the Queries & Connections task pane for managing data connections. Join us to unlock the full potential of Power Query and streamline your accounting processes.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

 
Who should attend: Professionals seeking to use Microsoft Excel more effectively.
 
Topics typically covered:

• Changing the data type for columns of data within the results grid.

• Managing the external data security warning that may appear when you link external data into Excel spreadsheets.

• Appending data from two or more worksheets into a self-updating consolidated list with Power Query.

• Overwriting one workbook that Power Query is referencing with another to roll the results forward to a new month, year, or other basis.

• Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more.

• Configuring Power Query queries to update automatically in the most efficient manner possible.

• Exploring the Queries & Connections task pane that shows data connections used within a given workbook.

• Extracting data from PDF files with Power Query in Microsoft 365.

• Creating a self-updating list of worksheets in any workbook with Power Query.

• Understanding how you can work backwards through applied steps in Power Query to visually step through data transformations.

• Navigating purposefully through worksheets by way of clickable hyperlinks.

• Transforming an accounting report by way of Power Query.

 
Learning objectives:

• Recognize the types of data-cleaning issues Power Query can and cannot resolve.

• Recall which command in Power Query enables you to return a data set back to an Excel workbook or data connection.

• State the location of the Refresh All command in Excel.

 
Level:
Basic
 
NASBA Field of Study:
Specialized Knowledge (2 hours)
 
Program Prerequisites:
None

 
Advance Preparation:
None