Data Analytics Modules
Module 1: Data Extraction and Data Sources
During this introductory module to data analytics, participants will learn the basic terminology used in performing data analytics-based tasks or working with data analytics. You will gain an understanding of the sources of data and importance in combining multiple sources. You will also be introduced to and be able to practice the basics of Microsoft’s Power Query.
Module 2: Data Preparation using Get and Transform
This module will teach participants how to apply Power Query to extract, transform, and load data for analysis. You will learn how to manipulate data once it is loaded into the platform and will practice the manipulation during the course. Using the same data as the instructor, you will perform an exercise to clean and transform a basic general ledger with an emphasis on preparing the data for analysis.
Module 3: Formulas, Named Ranges, and Formula Accounting
Module 3 will introduce participants to table functions in Excel and Power BI. You will practice applying named ranges and naming conventions to data. Using exercises alongside the instructor, you will practice common formulas and functions within Excel. You will also learn about common pitfalls in formula implementation to avoid.
Module 4: Excel Tables
This module will teach you to apply Excel and Power BI tools and skills directly to tables in Excel. You will work alongside the instructor to create named tables and structured references, enter formulas, and prepare the tables for input into Power Query. You will also be introduced to the preparation of the first level of statistics, descriptive statistics. Emphasis will be placed on mean, median, and standard errors.
Module 5: Pivot Tables
Module 5 provides hands-on practice using PivotTables in Excel. You will learn and apply skills to create, structure, and analyze data using PivotTables; the benefits and limitations of PivotTables will also be discussed. You will be introduced to Power Pivot and Online Analytics Processing within Power Pivot.
Module 6: Data Modeling
This module focuses primarily on Power Query, Power Pivot, and V-LOOKUP skills. You will learn how to join tables using various techniques and add-ons in Excel. You will be taught how to utilize the tools while maintaining data integrity through hands-on data exercises. You will apply skills to create relationships and analyze the data using Power Query.
Module 7: Calculated Columns and Dimensions
Module 7 will focus on using Excel with Power BI and Power Query to organize and analyze data. You will primarily work with calculated columns generated from existing data. This module will walk through hands-on exercises to create calculated columns, add dimensions to existing data tables, and create hierarchies within the data tables, with special attention paid to Star Schema analytical modelling. To explore practical applications, you will apply the skills to journal entry testing.
Module 8: Advanced Formulas – Array Formulas and DAX Formulas
This module will introduce formula arrays and DAX formulas in Power BI and Excel. You will learn the skills to create and evaluate array formulas and DAX formulas and develop an understanding of implicit or explicit intersections and measures within the formulas. You will apply the understanding to data and work alongside the instructor to create calculated columns in Power Pivot using DAX formulas and array formulas.
Module 9: Artificial Intelligence – Basic Concepts in Excel
Module 9 serves as an introduction to artificial intelligence and how artificial intelligence is utilized in Excel. You will be introduced to correlation and trend functions in Excel, as well as the basics of linear regression and its application in Excel. This module will focus on artificial intelligence functions in Excel, including, but not limited to, forecasting, trend analysis, regression analysis, and predictive modeling.
Module 10: Visualizations and Reporting
This module will introduce you to data visualization and reporting using Power BI and Excel. You will understand the benefits of using Power BI to create data visualizations and how Power BI differs from previous functions in Excel for visualizations. You will participate in a hands-on exercise alongside the instructor to create dashboarding for journal entry testing. This module explains the relation among various tools for data analytics: Power Query, Power Pivot, PivotTables, DAX formulas, and storyboarding.
Want to pick and choose topics? Each of these 1-credit modules is available for individual purchase.
*Note that professional certificates are only granted to attendees who complete full certificate programs.