Preparing for Power BI
Certification Exam 70-778
Presented by Ted Pattison
Born Again Power BI MVP
Agenda
Preparing for the 70-778 Exam
• Queries and Datasources
• Data Modeling and DAX
• Reports and Dashboards
• Apps and App Workspaces
Skills Measured
• Consume and Transform Data with Power BI Desktop
(20-25%)
• Model and Visualize Data
(45-50%)
• Configure Dashboards in the Power BI Service
(25-30%)
Consume and Transform Data
• Connect to data sources
• Connect to databases, files, folders
• Import from Excel
• Connect to SQL Azure, Big Data, SSAS
• Perform transformations
• Design and implement basic & advanced transformations
• Apply business rules
• change data format to support visualization
• Cleanse data
• Manage incomplete data
• Meet data quality requirements
Model and Visualize Data – Part 1
• Create and optimize data models
• Manage data relationships
• Optimize models for reporting
• Manually enter data
• Use Power Query
• Create calculated columns, tables, and measures
• Create DAX queries for calculated columns, tables, and measures
• Create performance KPIs
• Calculate the actual, calculate the target, calculate actual to target
• Create hierarchies
• Use date hierarchies, use business hierarchies, resolve hierarchy
issues
Model and Visualize Data – Part 2
• Create and format interactive visualizations
• Select a visualization type
• Configure page layout and formatting
• Setup visual relationships
• Configure duplicate pages, handle categories that have no data
Setup default summaries and categories,
• Position, align, and sort visuals
• Enable and integrate R visuals
• Format calculated measures
Model and Visualize Data – Part 3
• Manage custom reporting solutions
• Use Power BI API
• Use Microsoft Power BI Embedded
• Enable developers to create dashboards with custom applications
• Enable developers to embed dashboards in applications
• Authenticate a Power BI web application
• Enable developers to create custom visuals
Configure Dashboards – Part 1
• Configure a dashboard
• Connect to the Power BI service
• Publish connections to services by using SSAS
• Publish visualizations including data
• Configure a dashboard
• Add text and images
• Filter dashboards
• Configure dashboard settings
• Customize the URL and title
• Enable natural language queries
Configure Dashboards – Part 2
• Publish dashboards
• Publish to web
• Publish to Microsoft SharePoint
• Publish to SQL Server Reporting Services (SSRS)
• Configure security for dashboards
• Create a security group by using the Admin Portal
• Share dashboard with users or security groups
• Integrate with Microsoft OneDrive for Business
• Configure row-level Security
• Configure gateways
Configure Dashboards – Part 3
• Configure organizational content packs and apps
• Create a content pack
• Publish a content pack
• Edit a content pack
• Package dashboards and reports
• Configure app workspace
Sample Exam Question #1
Agenda
Preparing for the 70-778 Exam
Queries and Datasources
• Data Modeling and DAX
• Reports and Dashboards
• Apps and App Workspaces
Power BI Desktop is an ETL Tool
• ETL process is essential part of any BI Project
• Extract the data from wherever it lives
• Transform the shape of the data for better analysis
• Load the data into dataset for analysis and reporting
CSV
File Power BI Desktop Project (PBIX)
Excel Power BI
Workbook Desktop Dataset
OLTP
Database
File-based Data Sources
• Power BI Desktop supports common file types
Supported Databases
• Power BI Desktop supports many database systems
Azure Data Sources
• Power BI Desktop supports many Azure data sources
Sample Exam Question #2
Examples of Basic Query Steps
• Rename column
• Convert column type
• Format column values
• Replace column values
• Expanding related column
• Merging columns
• Splitting columns
Sample Exam Question #3
Query Steps for Cleaning Data
• Trim
• Removes white space at start and end
• Clean
• Removes non-printable characters
• Group By
• Used to deduplify rows and create unique ID values
Sample Exam Question #4
Queries and the M Language
• Power BI Desktop based on "M" functional language
• Query in Power BI Desktop saved as set of M statements in code
• Query Editor generates code in M behind the scenes
• Advanced users can view & modify query code in Advanced Editor
Sample Exam Question #5
Sample Exam Question #6
Power BI Project Template Files
• PBIX project can be exported to project template file
• Template file created with PBIT file extension
• Generated template files contains everything except for the data
• PBIT template file can be imported to create new PBIX projects
• Template files are powerful when used together with parameters
• How are template files used?
• Export PBIX project to create a PBIT template file
• Import the PBIT template file to create a new PBIX project
export import
Sample Exam Question #7
Agenda
Preparing for the 70-778 Exam
Queries and Datasources
Data Modeling and DAX
• Reports and Dashboards
• Apps and App Workspaces
Data Modeling with Power BI Desktop
• Steps to create a data model with Power Pivot
• Model imported tables using a star schema
• Create relationships between tables
• Modify columns (formatting, data category, etc)
• Create calculated columns and measures
• Create dimensional hierarchies
• Add Calendar table(s)
Data Modeling using a Star Schema
• OLAP Modeling often based on Star Schema
• Tables defined as fact tables or dimension tables
• Fact tables related to dimension table using 1-to-many relationships
Sample Exam Question #8
Table Relationships
• Tables in data model associated with relationships
• Relationships based on single columns
• Tabular model supports [1-to-1] and [1-to-many] relationships
• Relationships based on single column in each table
Relationship Properties
• Cardinality
• Cross filter direction
The RELATED Function
• RELATED function performs cross-table lookup
• Effectively replaces older VLOOKUP function
• Used in many-side table to look up value from one-side
• Used to pull data from lookup table into primary table
Sample Exam Question #9
Sample Exam Question #10
Calculated Columns vs Measures
• Calculated Columns (aka Columns)
• Evaluated based on row context
• Evaluated when data model is loaded into memory
• Defined within scope of table inside data model
• Measures
• Evaluated at query time based on current filter context
• Commonly used for aggregations (e.g. SUM, AVG, etc.)
• Defined at scope of data model
Sample Exam Question #11
Sample Exam Question #12
Working with DAX
• DAX is the language used to create data model
• DAX stands for "Data Analysis Expression Language"
• DAX expressions are similar to Excel formulas
• They always start with an equal sign (=)
• DAX provides many built-in functions similar to Excel
• DAX Expressions are unlike Excel formulas…
• DAX expressions cannot reference cells (e.g. A1 or C4)
• Instead DAX expressions reference columns and tables
=SUM('Sales'[SalesAmount])
Types of DAX Functions
• Date and Time Functions
• Information Functions
• Logical Functions
• Mathematical and Trigonometric Functions
• Statistical Functions
• Filter Functions
• Text Functions
• Time Intelligence Functions
Sample Exam Question #13
Sample Exam Question #14
Sample Exam Question #15
Geographic Field Metadata
• Fields in data model have metadata properties
• Metadata used by visuals and reporting tools
• Used as hints to Bing Mapping service
• “Tampa, FL” value mapped as Place and not as City
Sample Exam Question #16
Limitations of DirectQuery
• DirectQuery imposes the following limitations
• All tables must come from a single database
• Many types of query steps are not supported
• You cannot convert column type in a query
• No special treatment of date columns
• Calculated columns only allowed since June 2017
• By default, limitations placed on DAX in measures
Agenda
Preparing for the 70-778 Exam
Queries and Datasources
Data Modeling and DAX
Reports and Dashboards
• Apps and App Workspaces
Visuals (aka Visualizations)
• Reports are designed using visual (aka visualizations)
• Each visuals is based on an underlying visualization type
• Visualization type can be changed using Visualizations pane
• Visuals creating by using fields from tables inside Fields list
Power BI Licensing
• Microsoft initially offered two Power BI licensing options
• Power BI Free license
• Power BI Pro license ($10/month)
• Everything has been running within a shared capacity
• Microsoft recently introduced Power BI Premium licensing
• Power BI Premium customers can create dedicated capacities
• Power BI Premium licensing has monthly fee for dedicated capacity
• Dedicated capacity remove limits on upload size and # of refreshes
• Dedicated capacity can serve Power BI content to non-licensed users
• More info at https://powerbi.microsoft.com/en-us/pricing/
• Please ask questions about licensing offline and not during lectures
Sample Exam Question #17
Sample Exam Question #18
Agenda
Preparing for the 70-778 Exam
Queries and Datasources
Data Modeling and DAX
Reports and Dashboards
Apps and App Workspaces
Sample Exam Question #19
Sample Exam Question #20
Summary
Preparing for the 70-778 Exam
Queries and Datasources
Data Modeling and DAX
Reports and Dashboards
Apps and App Workspaces
Critical Path Training
https://www.CriticalPathTrainig.com
• PBI365: Power BI Certification Bootcamp – 4 Days
• Audience is Business Users, Analysts and Data Professionals
• Provides hands-on introduction to the Power BI platform
• Focuses on build solutions using Power BI Desktop
• Query design, data modeling and report and dashboard design
• Apps and App Workspaces
• Learn about “import” vs “connect to” with Excel workbooks
• PBD365: Power BI Developer Bootcamp – 4 Days
• Audience is Professional Developers
• Teaches developing custom visuals with TypeScript and D3
• Teaches R programming and integrating R with Power BI
• Teaches programming with the Power BI APIs
• Teaches developing with Power BI Embedded
