Power BI
The Complete Guide
Power BI Desktop
What the Desktop application is perfect for
Workflow of Power BI Desktop
Power BI Desktop
Data Data
preparation Data modelling
visualization
Query Relationship Data Report
Editor View View View
The Query Editor
How we import and prepare our data
Power BI Desktop – Query Editor
Power BI Desktop
Data Data
preparation Data modelling
visualization
Query Data Relationship Report
Editor View View View
The Star Schema
FACT TABLE VS DIM TABLE
The Star Schema
DIM TABLE DIM TABLE
Products Customers
• IdentifierProd FACT TABLE • IdentifierCust
• ProductType • FirstName
• PricePerUnit Sales • SecondName
• CostperUnit • Age
• IdentifierProd • Gender
• IdentifierDate
• IdentifierCust
• IdentifierGeo
• UnitsSold
Time SalesPoint
• TotalSales
• IdentifierDate • TotalCost • IdentifierGeo
• Year • Continent
• Quarter • Country
• Month • City
• Week
• Day
Our Project – Current structure
Population-Combined
• Country-ID
• Country
• Year
• AgeGroup
• Gender
• Population
Out Project turned into a Star Schema
FACT TABLE
DIM TABLE DIM TABLE
Population
Region Age
• Country-ID
• Country-ID • AgeGroup-ID • AgeGroup-ID
• Country • Year • AgeGroup
• Region • Gender • Category
• Population
Query: Duplicate vs. Reference
Query Editor
A Query 1 Query 2
B (Created in Query Editor) (Reference to Query 1)
A
Source
B
file A Query 2
C (Duplicate of Query 1)
B
Merge Queries - Join Kind
Merged Queries
LEFT RIGHT FULL
ID Sales Region ID Region Sales ID Sales Region
Separate Queries A 10 USA A USA 10
A 10 USA
Outer
Query 1 Query 2 B 50 n/a BB Europe n/a
B 50 n/a
LEFT RIGHT
C 20 Asia C Asia 20
C 20 Asia
ID Sales ID Region
BB n/a Europe
A 10 A USA
B 50 BB Europe ID Sales Region ID Region Sales
Anti
C 20 C Asia B 50 n/a BB Europe n/a
ID Sales Region
Inner
A 10 USA
C 20 Asia
Import data into the data model
Import data
Query 1
Query 2 Default = Query 1 &
Enable load is Query 2 are
Data preparation set for all loaded into the
Data model
queries data model
Query Editor Data View/Report View
Source files
Enable load is Query 1 is
Data preparation only selected loaded into the Data model
Import data for Query 1 data model
Query 1
Query 2 Query Editor Data View/Report View
Data View & Relationships
How we model our data
Power BI Desktop – Data Model
Power BI Desktop
Data Data
preparation Data modelling
visualization
Query Data Relationship Report
Editor View View View
Query Editor vs. Data Model
Query Editor Data Model
Connect to source files Add relationships
Clean data Add calculated columns
Shape data Add measures
Structure + prepare data Analyse data
Power BI Desktop – Data Model
Power BI Desktop
Data Data
preparation Data modelling
visualization
Query Data Relationship Report
Editor View View View
Let‘s bring our Data Model to live
Cardinality Cross Filter Direction Active Properties
= „Type of relationship“
One to many (1:*) & Many to one (*:1)
Customers Orders
ID-Customer FirstName SecondName ID-Order OrderDate ID-Customer
1 Maximilian Schwarzmueller A 01 Jan 2017 1
2 John Meyer B 08 Jan 2017 2
3 Linda Belle C 15 Jan 2017 1
4 Manuel Lorenz D 25 Jan 2017 1
E 05 Feb 2017 3
F 15 Feb 2017 4
Each customer is unique Each customer can have
multiple orders
One to one (1:1)
ID-Passport Valid Issued FirstName SecondName Country
1 2025 2005 Maximilian Schwarzmueller Germany
2 2019 1999 John Meyer USA
3 2017 1997 Linda Belle Japan
Passport Person
ID-Passport Valid Issued ID-Passport FirstName Second Name Country
1 2025 2005 1 Maximilian Schwarzmueller Germany
2 2019 1999 2 John Meyer USA
3 2017 1997 3 Linda Belle Japan
Power BI Desktop – Data Model
Power BI Desktop
Data Data
preparation Data modelling
visualization
Query Data Relationship Report
Editor View View View
One tool - Two languages
Description Application areas
Power Query Formula Language Prepare your data before you load
M-Language
Data transformation them into the data model
Independent from
each other
Data Analysis Expression Language
Create formulas for an in-depth
Analytical data calculation DAX-Language
analysis in the Data View
Comparable to Excel functions
Course interim conclusion
This course
M OR DAX
Calculated Columns vs. Measures
Perform an operation that generates results for each row of your table Calculated Column
Return a single result of a calculation or an aggregated value (e.g. Averages) Measure
Report View
Let‘s create beautiful charts and tables
Power BI Desktop – Report View
Power BI Desktop
Data Data
preparation Data modelling
visualization
Query Data Relationship Report
Editor View View View
Power BI Service & Power BI Mobile
We finished our work locally, what now?
Ways to continue
Single User Organization
Power BI Desktop Power BI Desktop
YOU YOU
STOP - Publish - Publish
Power BI Service Power BI Service
YOU
- Access - Collaborate
YOU IT
Share
Power BI
YOU Mobile Marketing
Power BI Power BI
Service Mobile
Questions to be answered
How can we publish our data to Power BI Service?
How can we collaborate in Power BI Service?
How can we share data and specify what we want to share?
Changes in 2017
Until
Power BI Free Power BI Pro
31 May
Personal users Collaboration
• Access to all Pro
Databases
01 June • Increased Workspace
2017 Storage + Power BI Premium
• Improved refresh-
rates
Functional alignment with remaining differences in Large Scale BI
sharing and collaboration deployments
Publishing our project data to Power BI Service
Publish/
Connect to
File
Your computer Power BI Service
Personal
Gateway
Power BI Desktop
Dataset & Report
On-Premises
Server Power BI Service
Gateway
Collaboration
Create Dashboards
YOU
Power BI Service
Dataset & Report from
Power BI Desktop
Create Dashboards App Workspace
YOU IT
How can we share our results from the App workspace?
Dashboard
Report Publish App
Power BI Service Dataset
Dashboard, Report &
Dataset
Report Publish to Web
PRO Data created using Pro features, can only be shared with Power BI Pro Users!
