Power BI
Power BI Desktop
- Attached Project Files
- Data source settings
- Power BI Desktop Workflow
- Data Preparation
- Query Editor
- Data Modeling
- Data View
- Relationship View
- Data Visualization
- Report View
- Data Model Interface
- Query Editor Interface
Query Editor
- Connecting Power BI Desktop to Source Files
- Transform Data
- New Source
- Keeping & Removing Rows
- Remove Top Rows
- Table Settings
- Remove Alternate Rows
- Patterns
- Keep Range of Rows
- Use First Row as Headers
- Working with Filters
- Search will exclude those not listed
- Remove Empty Rows
- Can save without applying queries/apply later
- Appending Queries
- Column amount and names must be equal in initial queries
- Working with Columns
- Choose Columns
- Replacing Values
- Formatting Data & Handling Formatting Errors
- Detect Data Type
- Error Query Group, fixed with replace values
- Pivoting and Unpivoting
- Attribute-Value pairing
- Unpivoting - Attribute(Name)-Value become separate columns
- Pivoting - Separate Columns become Attribute(Name)-Value Pairs
- Splitting Columns
- By number of characters, delimiter, positions
- Delimiter is removed
- Creating Groups and Cleaning Up
- Group by folder
- Reorder columns, sorting by column
- Star Schema
- Dim Tables - Multiple
- Products, Time, Customers, Sales Point
- Fact Table
- Sales
- IDs from each Dim Table
- Using Star Schema in Project
- Population-Combined to Fact Population table
- DIM Region
- DIM Age
- Query Duplicates vs References
- Reference depends on Source
- Duplicate copies initial query
- Creating DIM-Region Table and Removing Duplicates
- Reference of population-combined
- Creating Tables Manually
- Edit Source
- Merging Queries
- Column in common
- Join Kind
- Common ID
- Outer Join
- Left, Full, Right
- Anti Join
- Left, Right
- Not found
- Inner Join
- Found in both
- Merging Queries Applied
- Inner Join, select Region Name only in expanded, don't use prefix
- DIM-Age Table
- Age group from population-combined
- Index column from 1
- Understanding "Extract"
- Different in Transform and Add Column ribbons
- Conditional Columns
- If-Else chain
- Creating FACT-Table
- Reference of population-combined utilizing age and country IDs instead
- Basic Math Operations
- Transform (Number Column)
- Performance Optimization
- Enable Load (right-click query)
- Choosing what is loaded in Data Model
Data Model: Data & Relationship View
- Understanding Relationships
- Data Modeling
- Data View
- Relationship View
- Cardinality: Many-to-One & One-To-One
- One-to-Many (1:*)
- Many-to-One (*:1)
- Each Customer unique
- Each Customer can have multiple Orders
- One-to-One
- Passport to Person
- Cross Filter Direction & Many-to-Many
- Connector table, need two to communicate
- Cross filter direction to Both
- Feed info both ways
- Understanding Active Properties
- Deactivate relationship
- New relationships will be inactive by default if one already exists
- M-Language vs DAX (Data Analysis Expressions)
- One Tool - Two Languages
- M
- Description
- Power Query Formula Language
- Data Transformation
- Where to Apply
- Data Preparation
- Before Data Model
- DAX
- Description
- Data Analysis Expression Language
- Analytical Data Calculations
- Comparable to Excel Functions
- Where to Apply
- Create Insights
- In Data Model
- M vs DAX - A Practical Example
- M is logic behind query editor
- Advanced Editor
- Custom columns
- DAX
- Data view > Table Tools > New column
- Using default functions such as Concatenate
- Nesting functions within another
- DAX Basics
- DAX Reference
- Syntax:
Formula = ...
- Data Types: String, Number
- Operators: +, -
- Functions:
CONCATENATE()
- Advanced
- DAX Statements
- Define, Evaluate, Order By, VAR = DAX Queries
- DAX Syntax - Core Rules
Total Population = SUM(FactPopulation [Population Count])
- Formula Name
- Capital Letters
- Space
- SUM is DAX function, parenthesus
- Table Reference
- Capital Letters
- No Space
- Column Reference
- Square Brackets (always required)
- Capital Letters
- No Space
- Supported DAX Data Types
- String (Text)
- Whole & Decimal Numbers
- Boolean
- Date/Time
- Currency
- Blank (NA)
- DAX Operators
- Arithmetic
- +, -, *, /, ^
- Comparison
- =, ==, >, <, >=, <=, <> (not equal)
- Logical
- &&
- ||
- IN (multiple conditions)
- Text concat.
- &
- DAX Core Functions
- Text
CONCATENATE("X, "Y")
- Information
ISNUMBER(2020)
(true)
- Logical
IF([Population]>100000, "Big", "Small")
- Math
ROUND(352.867,2)
- Statistical
AVERAGE(Dim-Fact[Population])
- Filter
FILTER(Dim-Fact[Year]=2020)
- Date & Time
CALENDAR(DATE(2000,01,01),DATE(2020,12,31))
- CALENDAR Function
Calendar = CALENDAR(DATE(1990,01,01),DATE(2020,12,31))
DateQuarter = [Date].Quarter
- Applying DAX Basics
TotalRevenue = [RevenueProductA]+[RevenueProductB]
ProfitMargin = 1-(DIVIDE([TotalCost],[TotalRevenue],0))
- IF and RELATED
DayRating = IF([ProfitMargin]>0.65,"Good Day","Bad Day")
- RELATED has to have relationship
TotalUnitsSold = RELATED(UnitsSold[UnitsSoldProductA])+RELATED(UnitsSold[UnitsSoldProductB])
- Calculated Columns vs Measures
- Calculated Column
- Perform an operation that generates results for each row of your table.
- Measure
- Return a single result of a calculation or an aggregated value (e.g. Averages)
- First Measure
TotalPopulation = SUM([Population])
AveragePopulation = AVERAGE([Population])
- CALCULATE & FILTER
FILTER = (<table>, <filter>)
CALCULATE = (<expression>, <filter1>, <filter2>)
- Applying CALCULATE and FILTER in Measures
FemalePopulation = FILTER('FACT-Population',[Gender]="Female")
- Need aggregation for filter
FemalePopulationFilter = CALCULATE(SUM([Population]),FILTER('FACT-Population',[Gender]="Female"))
FemalePopulation = CALCULATE(SUM([Population]),'FACT-Population'[Gender]="Female",'FACT-Population'[Year]=2020)
- Creating Measures with Measures
MalePopulationFilter = CALCULATE(SUM([Population]),FILTER('FACT-Population',[Gender]="Male"))
- Remove year filter from FemalePopulationFilter
ShareFemalePop = [FemalePopulationFilter]/[TotalPopulation]
- Data Category
- Country, Continent, etc
Creating Visuals in the Report View
- Basic Visual Concepts
- Value area
- Population
- Legend
- Country Specific data
- Tooltip
- Age category
- Visuals & Reports
- Data/Drill (discussed later)
- Format
- Align, etc
- View tab
- Positioning, etc
- Visualizations column
- Has different charts, etc
- Elements
- Text, buttons, images
- Creating First Visuals
- Fields column, areas
- Line chart
- Population by Year, Region
- Stacked column chart
- Population by Region
- Tooltips & Interactions
- Add further info
- Hovering over charts for details
- Add ShareFemalePop percentage calculation to tool-tip
- Selecting similar filter, axis affects all charts
- Format > Edit Interactions
- interactions can specify how visuals interact with one another with filtering, highlights
- Filter specifics on top of visual
- Hierarchies & Drill Mode
- Multiple axis
- Sequence of different detail levels for data
- Right click > create hierarchy
- Drill down specific parts of hierarchy
- Activate/Deactivate
- Data Colors & Conditional Formatting
- Format > Data colors
- Different depending on chart
- Conditional, based on sum (f(x) button)
- Formatting Report Pages
- General formatting options when no visuals selected
- Formatting Visuals
- General formatting options when a visual(s) selected
- Format painter to apply formatting from one to another
- Report Themes
- View > Themes
- The Slicer
- Using FACT-Population Year
- Affects other visuals
- Default Summarization & Sorting
- Arrow by value Population, Sum by default
- Sort x-axis
- Treemaps & Tables
- Another Slicer by Region Name
- Another Slicer by Age-Category
- Treemap Population by Country (Details)
- Syncing Slicers
- Prompt on copy
- View > Sync Slicers
- Filter Types (Visual, Page & Report)
- Filter column with no visual selected
- Report level > Filter by year
- Page level > Filter by Region
- Visual level > Top N by Population
- Working with (Multi) Row Cards
- Cards with multiple rows, applying filters, more data
- Combined Visuals & Waterfalls
- Secondary values; adds second y-axis
- Can tailor in formatting
- Combine line and column charts
- Waterfall, category, breakdown, values
- Custom Visuals
- Import, AppSource
Taking Project to the Cloud with Power BI Pro (Service)
- Power BI Pro
- Single User scenario
- Publish to access from other devices
- Organization
- Publish to Power BI pro
- Accessible by others using workspaces
- Service vs. Pro vs. Premium
- Power BI Pro Interface
- Navigation, settings, Apps, Get Data
- Workspaces
- Publish report, datasets, dashboard to your workspace and can share.
- Connecting Power BI Pro & Power BI Desktop
- Sign in to publish
- Get data from local file
- Report and Dataset imported
- Datasets
- Different in course, similar to Power BI Desktop?
- Reports
- Creation additional
- Can create new pbix file based on report created in Power BI Pro
- Dashboards
- Create tiles
- Pin tiles to dashboard
- My Workspace
- Lineage view
- Quick Insights
- Refreshing Data with Gateways
- Personal Gateway for local files
- Standard Gateway for server data
- Data source settings
- Setup gateway
- Refresh and scheduled refresh
- Sharing Data From My Workspace
- Can't share datasets
- Share link, to email
- Access menu
- Read access
- Collaboration Workspaces
- Create New Workspace
- Settings, Access menus in Workspace
- Workspaces vs Apps vs Content Packs
- Apps -> Content Packs
- Share Dataset, Report, Dashboard
- Publishing an App
- Premade apps