Nov 2023

⚒️ Save time and minimize rework by effortlessly connecting, cleaning, and reshaping data with a most powerful tool for Excel and Power BI.


Carl Seidman CPA | Microsoft MVP

Managing Data for Finance with Microsoft Power Query

Verizon

Cigna Healthcare

United Technologies

Cox Enterprises

In this intensive 2 half-day Power Query program, attendees will embark on a step-by-step journey through data management. We'll begin by introducing core concepts and a fundamental understanding of Power Query, a tool that enables seamless extraction and transformation of raw data from back-end systems to Excel or Power BI. The first phase of the course emphasizes understanding the Power Query interface and learning how to connect various data sources, including databases, spreadsheets, and online services. Attendees will discover how to import data effectively, reducing the time spent on manual data entry. The next phase focuses on data cleansing and manipulation. You'll learn how to filter, sort, and aggregate data. You'll discover the different ribbons designed for intuitive user-interface and how to apply these techniques to real-world financial application. This will include handling missing or erroneous data, which is often a challenging task in financial analysis. The final phase delves into data modeling. Attendees will explore how to create custom calculations, both through a clear user-interface and by using Power Query's formula language, M. These skills are essential for constructing sophisticated financial models and data models, providing the foundation for accurate forecasting and strategic planning. By the end of the training, participants will not only master Power Query's functionality but also acquire a new perspective on data analysis. They'll be equipped with the skills to automate complex data preparation tasks, freeing them to focus on insightful analysis and decision-making. This transformative experience is tailored to financial professionals who seek to enhance their efficiency and analytical abilities. Whether you're a new financial analyst, s seasoned FP&amp;A expert, or someone seeking to elevate data analysis capabilities, this course is a basic step towards a more data-driven and insightful future in your finance career. Seidman Financial is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.NASBARegistry.org. Seidman Financial is a division of Seidman Global LLC.

Importing data into Excel + Power BI is easy when you know how to transform it.

Financial analysts and FP&amp;As seeking to automate data formatting, preparation, and importing to enhance forecasting, planning, and analysis.

Accounting professionals wanting to elevate routine presentations to automated financial reporting.

Small business owners who want to adopt basic data cleaning processes to get the information they need.

Controllers who want to streamline their financial processes and save time when they import their data into files and reports.

Who is this course for

What you’ll get out of this course

<ul><li>Review month-end close, adjusting entries, and recurring financial analysis</li><li>Connect to various data sources and run queries to centralize disparate data channels.</li></ul>

Introduction to Power Query

<ul><li>Connecting to various data sources like databases, Excel files, web data.</li><li>Importing and refreshing data from different sources.</li><li>Managing and transforming connections.</li></ul>

Data Import and Connections

<ul><li>Import invoice data, accounts receivable aging, POs, and accounts payable to reconcile working capital</li><li>Import monthly sales data to analyze routinely</li><li>Import finance actuals for rolling forecasts.</li></ul>

Process flows to connect to back-end data and bring it into recurring financial analysis and reporting

<ul><li>Cleaning and transforming data using Power Query Editor.</li><li>Utilizing functions and expressions to modify data.</li><li>Handling missing values, duplicates, and data types.</li></ul>

Data Transformation Techniques

<ul><li>Writing and customizing M and DAX queries.</li><li>Building parameterized queries and reusing queries across workbooks.</li><li>Developing complex calculations and aggregations.</li></ul>

Advanced Query Techniques

<ul><li>Using Power Query to streamline financial forecasting and budgeting.</li><li>Designing financial models and KPI dashboards.</li><li>Analyzing trends and performing variance analysis.</li></ul>

Integration with Financial Analysis

<ul><li>Implementing best practices for performance optimization.</li><li>Troubleshooting common issues and errors.</li><li>Tips and tricks for efficient Power Query use in a financial context.</li></ul>

Best Practices and Troubleshooting

<ul><li>The course begins with an overview of Power Query and its integration with Microsoft's tools including Excel and Power BI.</li><li>Participants will learn the essentials of Power Query's interface, its capabilities through data transformation, and how it's a vital tool in finance, enabling data preparation and transformation.</li></ul>

<ul><li>This segment focuses on connecting to various data sources, importing, refreshing, and managing data.</li><li>Participants will learn hands-on techniques to clean and transform data, making it ready for financial analysis and reporting.</li><li>By mastering these skills, they will enhance their ability to make data-driven financial decisions.</li></ul>

Data Acquisition & Management with Power Query

<ul><li>The business case challenges attendees to connect to external data sources, import into Power Query, sanitize and transform the data, and import it into Excel for forecasting and analysis.</li><li>The query will be archived and positioned to run for all future data imports.</li></ul>

Case Example: Replace Manual Imports with Automation through Power Query

<ul><li>Delve into the world of data modeling by exploring the Microsoft Engine.</li><li>Understand how to create and manage relationships between tables, define hierarchies, and build dynamic models that enable insightful financial analysis.</li><li>This module lays the groundwork for complex financial modeling, linking various data elements cohesively.</li></ul>

Introduction to Data Modeling with the Microsoft Engine

<ul><li>This module introduces DAX, a formula language used in Power BI, Power Pivot, and Power Query.</li><li>Participants will learn the basics of DAX expressions, calculations, and functions, all tailored to meet the specific needs of financial data analysis.</li><li>Engaging in real-world scenarios, students will experience how DAX can bring data to life.</li></ul>

Introduction to Data Analysis for Expressions (DAX)

<ul><li>Explore phase two of business case application in finance and FP&amp;A.</li><li>From building financial models and dashboards to performing trend analysis and variance analysis, this section ensures a practical understanding of Power Query's relevance and efficiency in finance.</li></ul>

Application of Power Query to Finance

Course syllabus

Meet Carl, your instructor and guide

Forensic Technology Services Manager at BDO USA, LLP

Forensic Technology Services Senior Associate, BDO USA

What Other Attendees Have Said

This course builds on live workshops and hands-on projects

Active hands-on learning

You’ll be interacting with other learners through breakout rooms and project teams

Interactive and project-based

Join a community of like-minded people who want to learn and grow alongside you

Learn with a cohort of peers

Learning is better with cohorts

Frequently Asked Questions

Students receive a certificate and 8.0 continuing professional education (CPE) credits for completing the course.

<a href="https://maven.com/seidman-financial/the-fractional-cfo-power-skills-program" rel="noopener noreferrer" target="_blank">Click here for more info</a>

The Fractional CFO Power Skills Mastermind

<a href="https://maven.com/seidman-financial/excel-finance" rel="noopener noreferrer" target="_blank">Click here for more info</a>

Dynamic Excel for Finance

<a href="https://maven.com/seidman-financial/financial-modeling" rel="noopener noreferrer" target="_blank">Click here for more info</a>

Advanced Financial and Business Modeling

<a href="https://maven.com/seidman-financial/cash-flow-forecasting-and-modeling" rel="noopener noreferrer" target="_blank">Click here for more info</a>

Cash Flow Forecasting and Modeling

<a href="https://maven.com/seidman-financial/managing-data-for-finance-with-power-query" rel="noopener noreferrer" target="_blank">Click here for more info</a>

Managing Data for Finance with Power Query

<a href="https://maven.com/seidman-financial/financial-planning-and-analysis" rel="noopener noreferrer" target="_blank">Click here for more info</a>

Financial Planning & Analysis (FP&A) Technical and Strategic Mastery - Driving Growth and Enhancing Value

<a href="https://maven.com/seidman-financial/delivering-financial-presentations-with-executive-presence" rel="noopener noreferrer" target="_blank">Click here for more info</a>

Delivering Financial Presentations with Executive Presence

Forecasting and Valuing Major Capex Projects

Data Visualization and Power BI for Finance

Explore Other Courses with Carl Seidman

What Attendees Have Said


Submit your information and we'll be in touch.

Course completed by

About this course

Get notified about the next cohort

Learn from live sessions