INTRODUCTION FOR VDA 6.5 PRODUCT AUDIT
In today’s fast-paced business environment, data is crucial for decision-making. However, manually updating and analyzing data in Excel can be time-consuming and inefficient. This workshop introduces you to Power Query and Power Pivot, two powerful tools in Excel that allow you to automate data transformation and modeling with just a few clicks.
With Power Query, you can extract, clean, and combine data from multiple sources without writing a single line of code. Meanwhile, Power Pivot enables advanced data analysis and modeling, making it easier to create insightful reports. By the end of this workshop, you will be able to automate your data processes, saving valuable time and improving accuracy.
Power Query is the recommended experience for discovering, connecting to, and importing data. Power Pivot is a powerful data analysis and data modeling tool. Use Power Query and Power Pivot to mold your data in Excel so you can explore and visualize it with PivotTables, Pivot Charts and Power Map.
LEARNING OBJECTIVE
By attending this workshop, participants will:
- Learn how to automate data extraction and transformation using Power Query
- Understand data modeling techniques with Power Pivot
- Improve efficiency in data analysis and reporting
- Eliminate repetitive manual tasks in Excel
- Gain hands-on experience with real-world data scenarios
WHO SHOULD ATTEND
This workshop is essential for analysts, managers, business owners, executives, and anyone responsible for data analysis to gain business insights. It is highly recommended for:
- Excel Users: If you use Excel daily and want to maximize its full potential for reporting.
- IT and BI Professionals: Those responsible for company data management and who need to support analysts in extracting data using Power Query and Power Pivot.
BENEFITS OF ATTEND
By joining this workshop, you will:
- Gain hands-on experience with Power Query and Power Pivot.
- Automate repetitive data transformation processes.
- Improve data accuracy and consistency.
- Enhance your reporting and data analysis skills.
- Save significant time in managing and analyzing data.
COURSE CONTENTS
Module 1: WHAT IS POWER QUERY?
- Extracting, Transforming and Loading (ETL) data
- Power Query vs “normal” Excel
- Importing data with Power Query
- Sources of data:
- Excel
- CSV/Text
- SQL Database
- Web
- Others
- Query Editor ribbon, Navigator pane & Preview grid
- Query settings panel – recording of transformation steps
Module 2: DATA CLEANING & FORMATTING
- Data types: Number, Date, Text, True/False
- Remove Columns, Remove Other Columns & Choose Columns
- Removing duplicates from a data set
- Replacing values and errors
- Null values: Replace, fill or filter out
- Removing whitespaces and non-printable characters
Module 3: DATA TRANSFORMATION TECHNIQUES
- Transpose, Unpivot Columns and Pivot Columns
- Text transformations:
- Extract texts before delimiter
- Extract texts after delimiter
- Add Prefix/Suffix
- Convert texts to uppercase/lowercase
Module 4: COMBINING DATA (QUERIES)
- Difference between merge and append
- Merge queries (the V-Lookup in Power Query)
- Append queries (combining tables with the same headers)
- Use cases of merge and append queries
Module 5: ORGANISING POWER QUERY AUTOMATION
- Best practices in organizing your data sources
- How to automate your data refresh
- Importing multiple files located in a folder
- Update Power Query for change in location of source data
- Change the default “Close & Load” options
Module 6: POWER QUERY TIME SAVING TIPS
- Copy and paste queries to another Excel workbook
- Duplicate and reference queries
- Preview or navigate to table objects
- Navigate through complex query dependencies
Module 7: POWER PIVOT CONCEPTS
- Extracting information from data with Power Pivot
- Uncovering data interpretation issues
Module 8: POWER PIVOT DATA MODEL
- Data Terminology
- Defining a consolidated view of data
- Generating a data mashup from structured and unstructured data sources into a data model
- Deriving relationships from data sources with the Relationship tool and the Diagram View
- Acquiring data from related tables
- Create linked data
- Defining calculated columns
Module 9: USING PIVOTTABLE WITH POWER PIVOT
- Create PivotTable with data models
- Analyze and summaries data with PivotTable
- Creating hierarchies in data model
- Data visualization with Pivot Charts
Module 10: IMPLEMENTING DAX FUNCTIONS IN POWER PIVOT
- Expressing information with measures
- Understand the difference between calculated columns and measures
- Understand the difference between implicit and explicit measures
- Exposing hidden information from data
- Quantifying and mining information with DAX functions
- Evaluating expressions with the CALCULATE() function and filter functions
Module 11: Power BI – Standard & Custom Visualizations
- Building Standard Visualizations
- Techniques for deciding on a Visual
- Introduction to Custom Visualizations
- Formatting Visuals
