Microsoft Power BI Data Analyst Professional On-Demand Training

In this lesson we will introduce the course

Installing Power BI Desktop

Introduction to business intelligence

In this lesson we discuss the 5 types of data analytics:

Descriptive Analytics
Diagnostic Analytics
Predictive Analytics
Prescriptive Analytics
Cognitive Analytics

• Introduction to Power BI: Power BI and its key features
• Overview of the Power BI ecosystem: Power BI Desktop, Power BI Service (Power BI online), and Power BI Mobile
• Exploring the capabilities and benefits of Power BI for data analysis and visualization
• Understanding the role of Power BI in business intelligence and decision-making processes

• Introduction to Power BI Desktop: Layout and key components
• Understanding the different panes and their functions: Report view, Data view, Fields pane, Visualizations pane, and Filters pane
• Exploring the various options in the Power BI ribbon: Home, Modeling, View, and Format tabs
• Working with multiple views: Report view, Data view, and Relationship view

• Introduction to data sources in Power BI: Supported file types and databases
• Connecting to local and cloud-based data sources
• Importing data from CSV files: Configuring delimiter settings and handling data formatting

For this lesson we will use the miles per gallon (mpg) dataset available at https://raw.githubusercontent.com/bpkindo/datasets/main/mpg.csv

Basic DAX Formulas

Importing data from excel to Power BI Desktop.
The data we use for this data is the AdventureWorks sample data available at https://github.com/bpkindo/datasets

Installing SQL Server, SQL Server Management Studio and AdventureWorks sample database

• Introduction to the integration between Power BI and SQL Server: Benefits and scenarios
• Connecting Power BI to SQL Server: Importing data from SQL Server databases

Often relational databases contain large amount of data that it may not be appropriate to import the entire table to Power BI. In such cases, we summarize data using SQL code, utilize the advanced option to import data to Power BI

- DirectQuery in Power BI: Configuring DirectQuery mode and utilizing SQL Server as a DirectQuery source
- Understanding performance considerations and limitations
- When to use DirectQuery and Import

• Importing data from various sources: Databases, web, SharePoint, and other data connectors

Data link: https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States

SQL programming language is one of the most widely used languages in data analytics. This exercise gives you a very brief overview of few of the most commonly used SQL clauses.

Exploring the Power Query Editor interface: Query settings, query steps, and applied transformations

Data link: https://raw.githubusercontent.com/bpkindo/datasets/main/DSD_PTD_RY24_P04_V10_DY22_BGM.csv

Hands on exercise cleaning and merging data using Power Query Editor

Continue learning the data cleaning capabilities of Power Query Editor

Replacing Values, Filling Values, and Unpivoting

In this lesson we will learn how to combine multiple queries/tables into one by appending them with each other

Data analysis often requires joining one table to another one. In this lesson we will discuss how you can join/merge two Power BI queries/tables.

It is important to assign correct data types to Power BI datasets. In this lesson we will illustrate issues that could arise when correct data types are not used.

- Briefly discuss adventureworks database used as illustration
- Autodetect relationships
- Manage relationships that is autodetected, or create relationships if not autodetected
- Introduced 1 to many relationship - cardinality
- Other topics discussed:
o Import two tables sales, and customer table
o Introduce the concept of fact table vs. dimension table that we will discuss in future lessons
o Expose students to concepts of surrogate key, and foreign key

We investigate the role of relationships in Power BI Data modeling

Cardinality of relationships
- 1 to many relationship
- many to 1 relationship
- 1 to 1 relationship

Active vs. inactive relationships

A star schema is a way to organize data easy analysis. It uses two main table types: fact tables store the measurable data, like sales amounts, and dimension tables contain descriptive attributes like product details or time. The fact table sits in the center, like a star, with dimension tables branching out, connected by foreign keys. This structure allows for fast querying and analysis of large datasets by letting you slice and dice the data based on different dimensions.

• Designing effective report layouts: Organizing visuals, utilizing pages, and managing interactions
• Applying report-level filters: Filtering visuals based on specific criteria or user selections

The purpose of an analytic report is to communicate data insights. Following basic design principles such as placement of charts, aesthetic balance, and contrasts aids in achieving such objective.

This lesson explores some design principles to consider when authoring Power BI reports.

• Cards
• Pie Charts
• Donut Charts
• Bar charts
• Column Charts

Exploring area charts and ribbon charts

Practice exercise to create a report for an animal shelter. Instructions for the exercise are in the power point file.

Table and matrix visuals allow us to show multiple metrics of multiple data slices in one visual.

We will learn how to create these visuals in Power BI and how applying conditional formatting can help report consumers quickly understand relationships.

Illustration of calculating columns using DAX

Understanding row context and filter context used in DAX calculations

Introducing simple DAX functions that take only one argument/input.

DAX Iterator functions like SUMX, AVERAGEX, RANKX, etc are explored

Illustration of how we can modify a filter context using CALCULATE() DAX function

In this lesson we discuss the DAX function RELATED using a practical example

Illustration of the DAX function RELATEDTABLE() and highlight is difference from the DAX function RELATED.

We continue to discuss several commonly used DAX functions

Introduction to DAX time intelligence functions.

• Publishing reports to Power BI Service: Uploading Power BI Desktop files to the cloud

• Setting up Gateway
• Configuring scheduled refresh: Setting up automatic data refresh for published reports

• Creating and managing workspaces: Collaborating with team members on reports and dashboards
• Sharing reports: Sharing options

Using Power BI's AI engine to automatically generate reports in Power BI Service

• What are Dashboards?
• Why do we use Dashboards?
• How do we create a dashboard in Power BI?

Setting alerts as report consumers

Autocreating reports and pinning to dashboards

Subscribing to reports vs. Dashboard Alerts

Creating quick Insights

Pinning Generated Quick Insights to Dashboards

• Introduction to Advanced Analytics Capabilities of Power BI.

• Utilizing statistical calculations in DAX: Average, median, standard deviation, etc.

• Introduction to advanced analytics features in Power BI: Forecasting, clustering, and binning
• Using the Key Influencers visual: Identifying factors that contribute to a specific outcome or behavior
• Using the Decomposition Tree visual: Identifying factors that contribute to a specific outcome or behavior

• Connecting to Azure data sources: Azure SQL Database, Azure Data Lake Storage, Azure Analysis Services, etc.
• Utilizing Azure services in Power BI: Azure Machine Learning, Azure Cognitive Services, and Azure Data Factory
• Exploring Azure Synapse Analytics integration: Analyzing and visualizing data from Azure Synapse in Power BI

• Understanding Power BI Embedded: Embedding Power BI reports and dashboards into custom applications
• Implementing Power BI Embedded: Embedding visuals and reports using the Power BI Embedded API
• Managing Power BI Embedded capacity: Provisioning and configuring capacity for embedding scenarios

• Integrating Power BI with Azure Machine Learning: Utilizing machine learning models in Power BI reports
• Deploying Azure Machine Learning models in Power BI: Scoring data and generating predictions in reports
• Creating custom machine learning solutions: Building end-to-end machine learning pipelines in Power BI using Azure ML

• Incorporating R and Python scripts in Power BI: Leveraging R and Python for advanced data manipulation and analytics
• Utilizing R and Python visuals: Creating custom visuals using R or Python code
• Connecting to R and Python servers: Configuring Power BI to execute R and Python scripts for data transformations and calculations

• Introduction to big data analytics in Power BI: Analyzing and visualizing large datasets
• Connecting Power BI to big data platforms: Hadoop, Spark, and Azure HDInsight
• Applying big data processing techniques: Data exploration, data transformation, and machine learning on big data in Power BI

• Introduction to the Power BI mobile app: Features, benefits, and supported platforms
• Navigating the Power BI mobile app interface: Exploring the different sections and functionalities
• Configuring mobile app settings: Personalizing the app experience and notifications

• Designing reports for mobile consumption: Best practices for responsive and mobile-friendly layouts
• Optimizing visuals for mobile devices: Simplifying visuals, adjusting font sizes, and utilizing drill-through actions
• Creating mobile-specific report pages: Tailoring content and interactions for mobile users

• Collaborating on reports and dashboards: Sharing and co-authoring capabilities in Power BI
• Utilizing collaboration workspaces: Creating and managing workspaces for team collaboration
• Implementing version control and content management: Tracking changes and maintaining report versions

• Overview of Azure Synapse Analytics: Features, capabilities, and use cases
• Connecting Power BI to Azure Synapse Analytics: Importing and analyzing data from Azure Synapse
• Leveraging Azure Synapse Analytics for big data processing: Combining data storage, data integration, and analytics

• Integrating Power BI with Azure Synapse Analytics: Direct querying and live connection options
• Utilizing Power BI dataflows with Azure Synapse Analytics: Creating and managing dataflows using Azure Synapse as a data source
• Building Power BI reports and dashboards on Azure Synapse Analytics data: Creating visualizations and insights on Azure Synapse data

• Resume Writing Basics
• Showcasing Power BI Skills in the Resume
• Including Power BI Projects and Achievements
• Presenting Data Visualization Skills
• Incorporating Data Analysis and Problem-Solving Skills
• Data Governance and Security
• Communication and Collaboration Skills
• Personal Branding and Professional Development
• Resume Optimization for Applicant Tracking Systems (ATS)
• Resume Writing and Editing Techniques
• Resume Customization for Targeted Job Applications
• Cover Letter Writing and LinkedIn Profile Optimization
• Portfolio Development and Presentation
• Mock Interviews and Interview Preparation