Beskrivning av: Designing Data Models in Excel for Power BI Reports Training
This course focuses on designing BI Data Models to display in either an interactive Power BI report or an interactive Excel dashboard. This is both a fundamental Power BI course and an advanced Excel course that 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 PivotTable and a Power BI dashboard. This practical course provides flexibility and empowers you to choose how you want to present your business insights to help management make informed decisions.
Data Models in Excel for Power BI Delivery Methods
-
In-Person
-
Online
Data Models in Excel for Power BI Course Benefits
-
Transform and present sophisticated data to provide Business Intelligence (BI)
-
Actualise a data model from multiple sources with Power Pivot
-
Shape and prepare your data with Power Query
-
Mine and expose hidden information with Data Analysis eXpressions
-
Communicate business and data insights with Power BI reports and dashboards
-
Leverage continued support with after-course one-on-one instructor coaching and computing sandbox
Data Models in Excel for Power BI Outline
Important course information
Prerequisites
- Experience with Excel at the level of:
- Excel Data Analysis Course • course 195
- This course is applicable to all versions of Excel, from Excel 2010 to Excel 2021
Module 1: Identifying the Role of Power Pivot in Business Intelligence (BI)
Introducing the Business Intelligence Tools
- Exploring the Microsoft Business Intelligence tools
- Defining the requirements for a collaborative solution
- Identifying the steps to create a Power BI dashboard
Familiarisation with Power Pivot
- Navigating the Power Pivot interface
- Extracting information from data with Power Pivot
- Uncovering data interpretation issues
- Creating a simple dashboard
Module 2: Building the Data Model with Power Pivot
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
- Simplifying the data model
- Acquiring data from related tables
- Consolidating information with calculated columns
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
Module 3: Shaping Data with Power Query
Familiarisation with Power Query
- Navigating the Power Query interface
- Extracting, Transforming, and Loading (ETL) data
- Converting data formats with Power Query steps
- Parsing columns to aid analysis
Combining Data
- Merging tables of data
- Constructing a single data set from multiple sources with the same field headings
- Develop an automated solution to combining data
Filtering Data with Parameters
- Developing queries
- Removing duplicates from a data set
- Defining Excel tables as parameters for a Power Query
Module 4: Manipulating and Analyzing Data with the Data Analysis eXpressions (DAX) Language
Defining measures for business performance
- Distinguishing the role of measures
- Translating key business concepts into measures
- Providing context for measures within a PivotTable
- Determining between implicit and explicit measures
Implementing DAX functions in Power Pivot
- Expressing information with measures
- Exposing hidden information from data
- Troubleshooting and debugging DAX calculations
Exploiting data analytics with aggregation
- Quantifying and mining information with DAX functions
- Summarising 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 categorising time periods with Time Intelligence functions
Setting key business targets with KPIs
- Analysing performance with measures
- Gauging performance against goals
Module 5: Designing Interactive Power BI dashboards
Familiarisation with Power BI
- Deciding on where to develop a dashboard
- Importing an Excel data model into Power BI Desktop
- Developing a data model in the Power BI Desktop
- Crafting visualisations from the analytics
Building a Power BI dashboard
- Constructing Power BI visualisations
- Designing visual-level, page-level, and report-level filter
- Assembling a Power BI Dashboard
Intresseanmälan
Learning Tree International
Learning Tree är ett internationellt utbildningsföretag med över 40 års erfarenhet av att leverera utbildning till yrkesverksamma IT-proffs, projektledare, verksamhetsutvecklare och chefer. Vi erbjuder allt från enstaka kurser till globala utbildningsprogram, och vi hjälper våra kunder att införa hållbara processer som fungerar idag och förbereder...
Läs mer om Learning Tree International AB och visa alla utbildningar.