Learning Objectives
By the end of this course, learners will be able to:
- Identify the different Power BI products, including Power BI Desktop, the Power BI Service, Power BI Mobile Apps, and understand the differences between free and Pro subscriptions.
- Connect to a wide range of data sources, such as text files, Excel workbooks, folders, and web data, and import these data sources into Power BI.
- Use the Query Editor to clean and transform data, performing tasks such as renaming queries, modifying columns, splitting and merging columns, and using functions like Trim and Clean.
- Organize and model data by creating relationships, defining primary and foreign keys, setting cross-filtering directions, and creating composite keys.
- Write basic DAX expressions to create calculated columns and measures, and utilize DAX functions such as SUMX, CALCULATE, and RELATED to enhance data analysis.
- Create and customize visualizations, including column and bar charts, gauges, treemaps, and maps, and format them using themes, branding, and visual-level filters.
- Publish and share reports and dashboards in the Power BI Service, create app workspaces, and manage access through app workspaces and publishing processes.
- Optimize content for mobile use by creating mobile-friendly versions of dashboards and reports.
These objectives ensure that learners gain practical skills in connecting, transforming, modeling, visualizing, and sharing data, empowering them to use Power BI for comprehensive and effective data reporting.
Course Outline
Power BI Introduction
The Power BI Product
Power BI Desktop
The Power BI Service
Power BI Mobile Apps
Power BI Subscription
Connecting to Data Sources
Connecting to Text Files
Overview of the Query Editor
Closing and Reopening the Query Settings Pane
Renaming a Query
Using the Query Settings Buttons
Applying Changes Made in the Query Editor
Editing an Existing Query
Conclusion
Connecting to Excel Data
Worksheets, Tables and Name Ranges
Importing Excel Objects into Power BI
Recognizing the Icons
Conclusion
Connecting to a Folder of Data
Benefits of Connecting to a Folder
Connecting to the Folder
Updating the Folder
Conclusion
Connecting to Web Data
Examining the Source Data
Connecting to the Excel Data
Connecting to the Currency Exchange Rate Data
Query Editor: Remove Columns
Query Editor: Use First Row as Headers
Query Editor: Filtering out Unwanted Rows
Query Editor: Renaming a Query
Query Editor: Creating a Custom Column
Adding a Report Background Image
Automatic Detection of Relationships
Using the Table Visual
Creating Calculated Columns
Using the Slicer Visual
Conclusion
Cleaning and Transforming Data
Trim, Clean and Case
Isolating the Problem
Using the Trim Function
Using the Clean Function
Using the Split Columns Command
Using the Merge Columns Command
Using the Capitalize Each Word Command
Conclusion
House Keeping and Meta Data
Renaming Items
Renaming Columns
Renaming Query Steps
Adding Descriptions to your Steps
Conclusion
The Split Columns Command
Data Scenario
Handling Variable-Length Customer Codes
Splitting Customer Codes
Splitting Country and Sector Codes
Criteria for the Creation of Relationships in Power BI
Preparing the Countries Data
Preparing the Sectors Data
Creating Relationships
Creating a Treemap Visual
Creating a Filled Map Visual
Conclusion
Removing Unwanted Rows
Removing Header Rows
Removing an Excel Table Total Row
Conclusion
Replace Values and Fill Down
The Replace Values Command
Remove Errors and Replace Errors
The Fill Down Command
Conclusion
The Unpivot Columns Command
What is Pivoted Data?
Importing the Data
Using UnPivot Other Columns
Creating a Table Visual with Star Ratings
Adding a Background
Adding a Table Visual
Apply Formatting to the Table
Adding Star Ratings
Sorting the Table
Adding a Slicer for Category
Conclusion
Append Queries
Importing the Data
Removing the differences between columns
Using Append Queries as New
Conclusion
Merge Queries
Using Merge Queries as New
Specifying the Join Kind
Conclusion
Data Modelling
Adding Data to the Model
The Budget Table
The Clients Table
The Products Table
The Purchases table
The Sales table
The Staff Table
Examining the Data Model
Understanding the Model Structure
Analyzing Table Relationships
Creating relationships automatically
Automatic Relationship Detection
Reviewing Suggested Relationships
Creating relationships manually
Manual Relationship Configuration
Setting Cardinality and Cross Filters
Creating Composite Primary and Foreign Keys
Linking the Sales and Purchases Tables
Linking the Sales and Budget Tables
Creating a Date Table
The CALENDAR and CALENDARAUTO Functions
Syntax and Implementation
The FORMAT Function
Creating Calculated Columns
Adding New Data Fields
Using DAX for Calculations
Creating Measures
Creating a Table for Measures
Applying the SUMX Function
The RELATED Function
Retrieving Related Data
Using RELATED in Calculations
Visualization
Branding in Power BI Desktop
Adding a Page Background
Adding a Logo
Using Report Themes
The Card Big-Picture Visual
Displaying the Budget Variance
Displaying the Margin Variance
The Gauge Visual
Using Visual-Level Filters
Using the Format Painter
Configuring Gauges
The CALCULATE Function
Cross Filter Direction
Customizing Visual Interactions
Creating Stacked Bar Charts
Editing Visual Interactions
Collaboration and Sharing
Editing a Published Report in the Power BI Service
Publishing a Report
Accessing Reports in the Power BI Service
Editing and Saving Changes
Creating app workspaces
App workspaces versus apps
My Workspace
Creating and Configuring App Workspaces
Publishing to App Workspaces
The Consumer Experience
Creating Dashboards
Pinning Visuals to a Dashboard
Pinning Report Pages
Using Q&A Visuals
Publishing an app
Publishing Process Overview
The Consumer Experience
Where to Next?
The Power BI Mobile Experience
Logging into the Power BI Service
The Default Mobile Experience
Optimizing Dashboards for Mobile Devices
Optimizing Reports for Mobile Devices
