Maximize Excel: Intro to Power Query Part 2
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 powerful capabilities of Power Query to streamline your data analysis processes. Learn how to transform extensive reports with dozens of columns into filterable rows using the Unpivot command, and discover techniques for importing and cleaning text files to prepare for insightful analysis. David will also demonstrate how to combine data from multiple lists based on common fields without relying on complex formulas, as well as how to create self-updating financial spreadsheets that automatically pull data and resolve common issues found in exported reports. Join us to enhance your Excel skills and make your data management more efficient!

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:

• Creating self-updating financial spreadsheets by using Power Query pull data via automated queries that also overcome common issues in exported reports.

• Transforming reports that are dozens or more columns wide into filterable rows by way of the Unpivot command in Power Query.

• Utilizing Microsoft Query to relate data from two text files together into a single combined list.

• Adding custom columns within Power Query.

• Using the Advanced Editor in Power Query to get a bird's eye view of transformation steps as well as transfer Power Query settings between workbooks.

• Setting queries to refresh automatically when opening a workbook that contains Power Query results.

• Combining data from two or more lists together based upon a common field, much like using VLOOKUP, but without formulas.

• Filtering unwanted data out of Power Query results.

• Creating self-updating financial spreadsheets by using Power Query to pull data via automated queries that also overcome common issues in exported reports.

• Importing and cleaning up text files with Power Query in anticipation for data analysis.

• Tweaking Excel’s AutoRecover settings to raise the odds of recovering your work after an Excel crash.

• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP.

Learning objectives:

• State which menu the Unpivot Columns command appears on in Power Query.

• Recall the query property that instructs Excel to import a fresh copy of a data set when you open a workbook.

• State which ribbon tab in the Power Query Editor that the Merge Queries command appears on.

Level: Basic

Instructional Method: Group Internet Based

NASBA Field of Study: Specialized Knowledge (2 hours)

Program Prerequisites: None

Advance Preparation: None