Introduction to Data Preparation and Modelling in Excel
Before modelling data in Excel, we need to do some data preparation. This helps us analyze our financial data more efficiently, reduces errors and enables us to write simpler formulas for calculations. Data modelling can be described as the process of analyzing and defining the different data sources and the relationships between those data sources using visual representations.
In this introductory course, you will be introduced to the main concepts of data preparation and modelling, and the different elements and stages of data modelling in Excel, from importing the data to creating the data model to ultimately creating reports.
This online, video-led course is performed using Excel for Microsoft 365 and includes an eBook and practical examples that are yours to keep.
Note: This course can be taken individually or as part of the Comprehensive Excel Certificate Program.
This is an introductory course, to take a deeper dive into data preparation and modelling, see our Data Preparation: Cleansing, Modelling and Dashboards course.
You will learn about:
- Data modelling in Excel and the Data Analysis Expressions (DAX) language
- How to import data using Power Query in Excel
- Sample data transformations in Power Query and the M language for writing code in Power Query
- Modelling different tables in Excel
- Types of tables (Fact vs. Dimension tables)
- Types of relationships in PowerPivot
- Measures and calculated columns and implicit and explicit measures
- Evaluation contexts in DAX (Filter vs. Row context)
- RELATED() and RELATEDTABLE() functions
- Using the FILTER() function to filter tables in DAX
- ALL() function and the difference between ALL() and ALLSELECTED() functions
- CALCULATE() function
Who should attend?
- advanced Excel users
- IT professionals
- business intelligence educators
- consultants
- financial analysts
