Power BI Cheatsheet

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

1. JBK Academy
   
   
2. Udemy
   
   
3. Data Camp
   
   
4. Edx
   
   
5. Coursera