Power BI Core Concepts & Workflow Cheatsheet 📊
Module 1: Introduction to Microsoft Data Analytics & Power BI
Data Analytics Roles: Focus on Data Analyst responsibilities.
Power BI Licensing:
Free: Basic features, personal use.
Pro: Collaboration, sharing, content packs.
Premium: Dedicated capacity, larger datasets, advanced features (like paginated reports).
Power BI Suite: Includes Power BI Desktop, Power BI Service, Power BI Mobile.
Module 2: Getting Data 📥
Common Data Sources:
Excel Workbooks
Access Databases
Web (Online Sources)
Connectivity Modes:
Import: Data loaded into Power BI. Faster queries, but data is static until refreshed.
DirectQuery: Live connection to the source. Data is always current, but performance depends on the source.
Key Terms:
Data Source: Where the data originates.
Data Set: Data prepared for use in Power BI reports and dashboards.
Error Handling: Address missing source files, issues with Power View visuals.
Performance Tip: Cloud-stored files (OneDrive/SharePoint) offer better refresh capabilities than local files.
Module 3: Shaping Data (using Power Query Editor) 🛠️
Purpose: Clean, transform, and reshape data before loading.
Common Transformations:
Rename Tables/Columns
Promote First Row as Headers
Remove Columns
Filter Rows (e.g., remove nulls, specific values)
Sort Data
Merge Queries: Combine data from multiple tables (like SQL JOINs).
Data Profiling Tools: Assess data quality (column quality, distribution, profile).
Module 4: Data Modeling Fundamentals 🧱
Goal: Design efficient and scalable data models.
Table Operations:
Format Columns: Set data types (text, number, date), currency, percentages.
Categorize Data: For mapping (e.g., address, city, latitude, longitude).
Organization:
Dimensions: Descriptive attributes (e.g., Product, Customer, Date).
Hierarchies: Navigational paths (e.g., Year > Quarter > Month).
Model Relationships:
Define how tables connect.
Cardinality:
One-to-One (1:1)
One-to-Many (1:N) - Most common
Many-to-One (N:1)
Many-to-Many (N:N) - Use bridge tables if possible.
Security:
Row-Level Security (RLS): Restrict data access for specific users/roles.
Module 5: DAX Calculations (Data Analysis Expressions) 📈
DAX: Formula language for creating calculated columns, measures, and tables.
Key Concepts:
Calculated Columns: Computed row by row, stored in the model. Use for static values or row-context logic. [Column] = [Price] * [Quantity]
Measures: Aggregations calculated on the fly based on report context (filters, slicers). Do not store data. Total Sales = SUM(Sales[Amount])
Calculated Tables: Tables created using DAX formulas.
Quick Measures: Pre-built common calculations.
Common DAX Functions:
Aggregation: SUM(), AVERAGE(), MIN(), MAX(), COUNT(), DISTINCTCOUNT()
Logical: IF(), SWITCH()
Filter/Context: CALCULATE(), FILTER(), ALL(), ALLEXCEPT(), ALLSELECTED()
Date/Time (Time Intelligence): Often requires a Date Table.
DATEDIFF(): Difference between two dates.
CALENDARAUTO(): Creates a date table automatically.
YEAR(), QUARTER(), MONTH(), DAY()
FORMAT(): Format dates/numbers.
ENDOFMONTH(), ENDOFQUARTER()
TOTALYTD(), SAMEPERIODLASTYEAR()
Ranking: RANKX()
Table Manipulation: ADDCOLUMNS(), DISTINCT()
Key Performance Indicators (KPIs): Visual representation of progress towards a measurable goal.
Module 6: Optimizing Model Performance 🚀
DirectQuery: Consider for very large datasets or real-time needs (be mindful of source performance).
DAX Variables: Use VAR ... RETURN syntax in DAX measures for better readability and performance (avoid repeating calculations).
Optimization Techniques:
Restrictive Filters: Apply filters early in data import or visuals.
Limit Visuals: Avoid too many visuals on one page.
Evaluate Custom Visuals: Some custom visuals can impact performance.
Reduce cardinality of columns where possible.
Optimize data types.
Module 7: Designing and Building Reports 🖼️
Visual Selection (Examples):
Pie Chart/Donut Chart: Parts of a whole.
Clustered/Stacked Column/Bar Chart: Comparisons.
Multi-row Card: Display multiple key metrics.
Table/Matrix: Detailed data, crosstabs.
Slicer: Interactive filtering.
Scatter Chart: Relationship between two numerical values.
Line Chart: Trends over time.
KPI Visual: Track progress against a target.
Report Design Elements:
Tooltips: Show additional info on hover.
Legends: Identify data series.
Titles, Backgrounds, Shadows: Enhance visual appeal.
Data Labels: Display values directly on visuals.
Sorting & Filtering: Control data presentation.
Advanced Features:
Sync Slicers: Apply a slicer across multiple report pages.
Drill-through Pages: Navigate to a detailed page filtered to a specific data point.
Conditional Formatting: Highlight data based on rules (e.g., color cells in a table).
Bookmarks: Save specific report states (filters, visuals) for navigation or storytelling.
Accessibility:
Alt Text: Descriptions for visuals (for screen readers).
Tab Order: Define navigation order for keyboard users.
Themes: Use high-contrast themes.
Customization:
AppSource Custom Visuals: Extend visualization options.
Copy Visualizations: Duplicate visuals within or between pages.
Module 8: Creating Dashboards (in Power BI Service) 🖥️
Purpose: Provide a high-level, consolidated view of key metrics from multiple reports.
Creation: Pin Visualizations from published reports to a dashboard.
Dashboard Enhancements:
Add Videos, Text Boxes, Images, Web Content.
Apply Themes.
Key Features:
Tile Alerts: Notify users when data in a tile meets a specific threshold.
Q&A (Question and Answer): Ask natural language questions about your data.
Quick Insights: AI-powered feature to automatically find interesting patterns in your data.
Management:
Set a Featured Dashboard (default landing dashboard).
Manage Favorite Dashboards.
View Usage Metrics to see how dashboards are being used.
Mobile View: Configure dashboards for optimal viewing on mobile devices.
Module 9: Paginated Reports 📄
Purpose: Designed for printing or PDF export; ideal for operational reports, invoices, or when you need to see all data (can span many pages).
Key Characteristic: "Pixel-perfect" layout.
Availability: Requires Power BI Premium capacity for the workspace.
Authoring Tool: Power BI Report Builder (a separate standalone application).
This cheatsheet should give you a good overview of the key topics covered in the Power BI course!
