Here’s a Power BI Cheat sheet that includes some essential tips, features, and shortcuts for quick reference:
1. Key Components
- Reports: Visual representation of data (charts, graphs).
- Dashboards: Single page showing a collection of visuals from one or more reports.
- Datasets: Data loaded into Power BI.
- Workspaces: collaborative spaces to manage reports, datasets, and dashboards.
2. Data Loading & Transformation
- Data Source: Load data from multiple sources (Excel, SQL, Web, etc.).
- Power Query Editor: Tool to clean and transform data.
- Remove rows: Home→ Remove rows
- Replace Values: Transform→ Replace Values
- Split Column: Transform → Split Column
- Group By: aggregate data by specific columns.
3. DAX (Data Analysis Expressions)
- Basic Aggregations:
- SUM (column): sum of values in a column.
- AVERAGE (column): Average of values.
- COUNT(column): Count of values in a column.
- Logical Functions:
- IF(condition, true_value, false_value): conditional expression.
- SWITCH(expression, value1, result1, …): Multi-conditional expression.
- Date Functions:
- TODAY(): Returns the current date.
- DATEDIFF(start_date, end_date, interval): Difference between two dates.
- Time intelligence:
- TOTALYTD(expression, dates): Year-to-date calculation.
- PREVIOUSMONTH(column): Data from the previous month.
4. Common Visualizations
- Bar/Column Chart: Used for comparing categories.
- Line Chart: Visualizes trends over time.
- Pie/Donut Chart: Displays parts of a whole.
- Scatter Chart: Shows relationships between two numerical values.
- Card: Displays a single metric or key figure.
5. Formatting & Design
- Themes: Apply predefined or custom themes: View→ Themes.
- Conditional Formatting: Format visuals based on values.
- Interactions: Control how visuals interact: Format→ Edit Interactions.
- Tooltips: Show additional data when hovering over visuals.
6. Filters & Slicers
- Basic Filter: Filter visual data on a specific field.
- Slicers: Interactive filtering option to filter by category.
- Drillthrough: Navigate to detailed reports by right-clicking on a visual.
- Cross-filtering: Selecting data in one visual filters other visuals on the same page.
7. Power BI Service
- Publishing: Publish reports from Power BI Desktop to Power BI Service.
- Workspaces: organize reports and datasets in shared environments.
- Sharing: Share dashboards and reports with others using File → Share.
- Schedule Refresh: Automate data refresh at regular intervals.
8. Shortcuts
- Show Data Pane: Ctrl + Shift + D
- Duplicate Page: Ctrl + D
- Delete Page: Ctrl + Delete
- Refresh Data: F5
- Add New Page: Ctrl + N
9. Bookmarks & Drillthrough
- Bookmarks: Capture the current state of a report for easy navigation.
- Drillthrough: Right-click to open a more detailed report or page.
10. Power BI Performance
- Reduce Columns: Only include necessary columns in data models.
- Avoid calculated columns: Use calculated measures where possible.
- Disable Auto Date/Time: Go to Options → Data Load → Time Intelligence to turn off auto date/time to improve performance.
- Aggregations: Use aggregation functions to summarize large datasets.
11. Advanced Features
- Row-Level Security (RLS): Restricts data access for specific users.
- Define roles in Power BI Desktop: Modelling → Manage Roles.
- Assign users to roles in Power BI Service.
- Power BI Dataflows: A way to ingest, transform, and load data into the Power BI service. Useful for handling large datasets and reusing transformation logic.
- Data Gateways: Allows secure data transfer between Power BI Service and on-premises data sources.
- Personal Gateway: For personal use.
- On-premises Data Gateway: For organizational use.
12. Power BI Mobile
- Mobile View: Design a report layout optimized for mobile devices.
- View → Mobile Layout: Drag and resize visuals to create a mobile-friendly view.
- Push Notifications: Receive real-time notifications on key performance indicators (KPIs) directly on mobile devices.
13. AI Features
- Key Influencers Visual: Identify factors that affect a particular result.
- Decomposition Tree: Drill down into data and discover insights by breaking down measures hierarchically.
- Q&A Feature: Use natural language to ask questions about your data.
- Example: Type “Total sales by country” in the Q&A box, and Power BI generates a visual for you.
14. Power BI Embedded
- Embed Reports: Integrate Power BI reports into custom applications using Power BI Embedded APIs.
- Useful for organizations wanting to embed reports into their web portals or internal applications.
15. Custom Visuals
- Marketplace: Access additional visuals from the AppSource marketplace.
- Insert → More Visuals → From AppSource: Download and integrate custom visuals created by the community.
- R & Python Visuals: Integrate R and Python scripts to build custom visuals.
- R: Perform complex statistical analysis or create specialised visuals.
- Python: Use libraries like matplotlib, seaborn, or pandas for advanced data visualisation.
16. Best Practices for Report Design
- Keep it Simple: Avoid cluttered reports. Focus on key metrics.
- Use Consistent Colors: Define a color palette and stick to it to create visual harmony.
- Limit Interactions: Too many cross-filters can confuse users. Be selective about interactions.
- Tooltips: Use custom tooltips to display additional information without overwhelming the report.
- Page Navigation: Use buttons or bookmarks to create an intuitive flow between report pages.
17. Collaboration Features
- Comments: Users can add comments to reports or specific visuals in Power BI Service.
- Subscriptions: Users can subscribe to report pages and receive email notifications with snapshots of reports.
- Export to PowerPoint or PDF: Share static versions of reports.
- File → Export → PowerPoint/PDF.
18. Version Control
- Power BI Service Version History: Track changes in reports published to the cloud and revert to previous versions if necessary.
- Local Version Control: Use Git or other version control systems to manage Power BI files (.pbix) locally.
19. Connecting to APIs and Custom Data Sources
- Web API: Connect to REST APIs using the Web connector.
- Get Data → Web → Enter API URL: You can use APIs to pull data directly from services into Power BI.
- Custom Connectors: Create and use custom connectors for niche data sources.
20. Power BI and Microsoft Ecosystem
- Integration with Excel: Import Excel models and Power Query transformations directly into Power BI.
- SharePoint Integration: Load data directly from SharePoint lists.
- Teams Integration: Embed Power BI reports in Microsoft Teams for better collaboration.
- Azure Synapse Analytics: Power BI integrates with Azure Synapse for large-scale data processing and analysis.
21. Paginated Reports
- Paginated Reports: Ideal for detailed reports that span multiple pages (e.g., invoices, forms).
- Created using Power BI Report Builder and hosted in Power BI Service.
22. Power BI API
- REST API: Allows automation of Power BI tasks (e.g., publishing reports, embedding content).
- Common tasks include refreshing datasets, getting list of reports, and managing workspaces.
23. Measures vs Calculated Columns
- Measures: Calculations are done on the fly and depend on the filters applied to the visuals.
- Calculated Columns: Computed during data load or refresh and stored in the data model, consuming more storage space.
24. Data Model Optimization Tips
- Star Schema: Organize tables in a star schema (fact and dimension tables) for better performance.
- Avoid Calculated Columns: Use DAX measures instead to keep your data model lightweight.
- Remove Unused Columns: Delete unnecessary columns to reduce model size.
- Composite Models: Combine direct query and import data sources for more flexibility.
25. Deployment Pipelines
- Development Workflow:
- Development → Testing → Production.
- Power BI Deployment Pipelines help automate and streamline this process, enabling version control and environment-specific configurations.
Where You Can Learn Power BI
- JBK Academy
- Udemy
- Data Camp
- Edx
- Coursera
