Course Description
This course focuses on designing BI Data Models to display in either an interactive Power BI report or in an interactive Excel dashboard. This advanced Microsoft Excel/fundamental Power BI course will take you through a data journey from importing, cleansing and merging large amounts of data to analysing the data to presenting strategic data in a Power BI dashboard. This is a practical course designed to provide flexibility and to empower you to choose how you want to present your business insights to help management make informed decisions.
What you will learn
Identifying the Role of PowerPivot in Business Intelligence (BI)
Introducing PowerPivot
- Accessing the PowerPivot add-in
- Navigating the interface
- Loading a single data source
- Creating a dashboard
Employing PowerPivot in the BI process
- Extracting information from data with PowerPivot
- Uncovering data interpretation issues
Building the Data Model with PowerPivot
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 Relationships tool and the Diagram View
Denormalizing data to simplify usage within other BI reporting tools
- Acquiring data from related tables
- Defining calculated columns
- Consolidating information available to BI tools
Querying SQL Server data
- Designing queries to import data from SQL Server
- Relating tables with outer joins
Fixing common data issues with Power Query
- Extracting, Transforming and Loading (ETL) data
- Converting data formats with Power Query steps
- Parsing columns to aid analysis
- Removing duplicates from a data set
- Constructing a single data set from multiple sources with the same field headings
Manipulating and Analyzing Data with the Data Analysis eXpressions (DAX) Language
Defining calculated fields for business performance
- Distinguishing the role of calculated fields
- Translating key business concepts into calculated fields
- Providing context for calculated fields within a PivotTable
- Determining between implicit and explicit calculated fields
Implementing DAX functions in PowerPivot
- Expressing information with calculated fields
- Exposing hidden information from data
- Troubleshooting and debugging DAX calculations
Exploiting data analytics with aggregation
- Quantifying and mining information with DAX functions
- Summarizing and aggregating data from other tables with the X functions
- Evaluating expressions with the CALCULATE() function and filter functions
- Substituting values with the SWITCH() function
Mining for information with date and time analysis
- Grouping dates for time analysis
- Comparing and categorizing time periods with Time Intelligence functions
Setting key business targets with KPIs
- Analyzing performance with calculated fields
- Gauging performance against goals
Presenting Information with Dashboards
Articulating and analyzing data
- Drilling down into data using a hierarchy
- Managing data with perspectives
- Identifying patterns and trends in your PowerPivot data with Power View charts
- Classifying data into different geographical regions
Designing effective dashboards
- Contextualizing calculated fields with PivotTable slicers and Power View filters
- Documenting structures with Hierarchical Diagrams
- Visualizing and comparing performance matrices with Power View multipliers
- Globalizing location-based results to identify trends and patterns on a 3D scale with Power Map
Creating Team BI Solutions
- Defining the requirements for a team solution
- Coordinating results with team members
- Sharing a PowerPivot solution
Looking for Group eLearning Options?
Get your team access to 4,000+ top courses anytime, anywhere.
Request On-Site TrainingUpcoming Dates
-
Location: VA, Classroom Live
-
Date & Time: Apr 24, 2024 09:00 AM EDT
-
Location: , Virtual Live
-
Date & Time: May 22, 2024 09:00 AM EDT
Course Dates
| Location | Date & Time | Duration | Course Type | Price |
|---|
Cancelation Policy
If you cannot attend an event, you may send someone else in your place. If that isn’t an option for you, cancellations received up to five working days before the event are refundable, minus a registration service charge ($10 for one-day events; $25 for multiple-day events). After that, cancellations are subject to the entire seminar fee, which you may apply toward a future seminar. Please note that if you don’t cancel and don’t attend, you are still responsible for payment.
