Overview
This one-hour course will focus primarily on Power Query, Power Pivot, and VLOOKUP skills. Users will learn how to join tables using various techniques and add-ons in Excel. Users will understand how to utilize the tools while maintaining data integrity. The majority of the course will focus on hands-on data exercises. Users will apply skills to create relationships and analyze the data using Power Query.
This course is one in a ten-part certification series, Core Data Analytics: Excel and Power BI Skills for Accounting and Finance Professionals, by Surgent IQ.
Major Topics:
- How to join tables using
- VLOOKUP
- Power Query- append queries and merge queries
- Power Pivot- Creating a relationship via diagram view
- Types of relationships
- Data integrity
- Normalization
- Facts vs dimension tables
- Exercises- data modeling
- VLOOKUP and index match formula
- Appending data in Power Query
- Merging data in Power Query
- Connection only queries
- Creating a relationship in Power Pivot
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand types of data relationships that exist within the data (e.g., linear, normal, etc.)
- Apply VLOOKUP and index match formulas in Excel
- Append and merge multiple data sets into one
- Create relationships in Power Pivot
Designed for:
Accountants who want to transition to data analysis and have a basic knowledge of Excel and Excel formulas
Prerequisite:
Intermediate knowledge of Excel
Advanced Preparation:
Install Power Pivot and Data Analysis ToolPak