This chapter covers Power BI, Microsoft's business analytics service that transforms raw data into interactive visualizations and dashboards. For the MS-900 exam, Power BI falls under objective 2.5, which focuses on productivity solutions in Microsoft 365. While Power BI is not a core Microsoft 365 app like Exchange or Teams, it is part of the Microsoft 365 ecosystem and frequently appears in exam questions about analytics and reporting. Expect 1-2 questions on Power BI components, licensing, and integration with other M365 services. This chapter will give you the precise technical details needed to answer those questions correctly.
Jump to a section
Imagine you run a busy restaurant and need to create a new menu based on customer preferences, sales data, and ingredient costs. You have raw ingredients (data) in various places: supplier invoices, point-of-sale records, and customer feedback forms. You could cook everything yourself, but that would take hours and you'd likely make mistakes. Instead, you hire a personal chef service (Power BI). First, the chef collects all your ingredients from different sources (data connectors) and stores them in a well-organized pantry (Power Query and data model). The chef then cleans and prepares the ingredients—chopping vegetables, marinating meat—so they are ready to cook (data transformation). Next, the chef creates recipes (measures and calculated columns) that combine ingredients in specific ways to produce dishes (visualizations). These dishes are arranged on a menu (dashboard) that you and your staff can view anytime on tablets (Power BI Service). The chef can also send you a daily summary of what dishes are popular (scheduled refresh). Critically, the chef does not cook the same meal every day; they can slice and dice the data to answer ad-hoc questions, like 'What was the most popular dessert last Tuesday?' (interactive filtering). If a new supplier comes in, the chef can incorporate their data without starting from scratch (scalability). The chef's service is not free—you pay a subscription (Power BI Pro or Premium) based on how many people need to see the menu and how often you need updates. Without this service, you'd be drowning in raw ingredients, unable to see the big picture of your restaurant's performance.
What is Power BI and Why Does It Exist?
Power BI is a suite of business analytics tools that allows users to connect to various data sources, transform and model the data, and create interactive reports and dashboards. It was designed to address the challenge of data silos—where business data lives in different systems (Excel, SQL databases, cloud services like Salesforce or Azure) and is difficult to combine and analyze. Power BI provides a self-service analytics platform, meaning non-technical users can create reports without relying on IT. It also offers enterprise-grade features for sharing and collaboration.
Core Components of Power BI
Power BI consists of several components that work together: - Power BI Desktop: A free Windows application for creating reports and data models. It includes Power Query for data transformation, a data modeling engine, and a visualization canvas. Reports created in Desktop can be published to the Power BI Service. - Power BI Service: A cloud-based SaaS (Software as a Service) platform for sharing, collaborating, and viewing reports and dashboards. It is accessible via browser or mobile apps. The service also handles scheduled data refresh, data gateways, and workspaces. - Power BI Mobile: Native apps for iOS, Android, and Windows mobile devices that allow viewing and interacting with dashboards and reports. - Power BI Report Builder: A tool for creating paginated reports (like traditional SQL Server Reporting Services reports) that are formatted for printing or PDF generation. - Power BI Report Server: An on-premises solution for hosting Power BI reports, available with Power BI Premium or SQL Server Enterprise with Software Assurance.
How Power BI Works Internally
Let's walk through the typical workflow:
1. Connect to Data: In Power BI Desktop, you use connectors to pull data from sources like Excel, SQL Server, SharePoint lists, Dynamics 365, Azure SQL Database, or hundreds of other sources. The data can be imported into Power BI's in-memory columnar storage engine (VertiPaq) or queried directly via DirectQuery or Live Connection.
2. Transform Data: Power Query Editor allows you to clean and reshape data—remove columns, filter rows, merge tables, pivot/unpivot, and create custom columns. Each transformation step is recorded in M language, and the query is executed when data is loaded.
3. Model Data: You define relationships between tables (e.g., sales table linked to product table via ProductID). You can create calculated columns and measures using DAX (Data Analysis Expressions). For example, a measure Total Sales = SUM(Sales[Amount]). The data model is optimized for fast aggregation.
4. Create Visualizations: Drag fields onto the report canvas to create charts, tables, maps, etc. Visuals are interactive—clicking a bar in a chart filters other visuals on the page.
5. Publish to Service: Save the .pbix file and publish to the Power BI Service. You can create dashboards by pinning visuals from reports. Dashboards can include tiles from multiple reports and datasets.
6. Share and Collaborate: Share reports and dashboards with users via the service. Permissions are managed through workspaces (similar to SharePoint sites). Data refresh can be scheduled (up to 8 times per day with Pro, 48 times per day with Premium) using a data gateway if the data source is on-premises.
Key Values, Defaults, and Limits
Data refresh limit: Pro license allows 8 scheduled refreshes per day; Premium allows 48.
Data storage limit per user: Pro license: 10 GB per user; Premium Per User: 100 TB.
Dataset size limit: Pro: 1 GB; Premium: 10 GB (can be larger with large dataset mode).
Report sharing: Pro users can share reports with other Pro users. Free users can only view reports in workspaces assigned to Premium capacity.
Data gateway: Used for on-premises data sources. A single gateway can support multiple data sources. There are two modes: Standard (for multiple users) and Personal (for single user).
Integration with Microsoft 365
Power BI integrates with Microsoft 365 in several ways: - Integration with Microsoft Teams: You can add Power BI tabs in Teams channels to display reports. Also, Power BI bot can answer natural language queries. - Integration with SharePoint Online: Embed Power BI reports in SharePoint pages using the Power BI web part. - Integration with Excel: Analyze in Excel feature allows users to connect to Power BI datasets from Excel pivot tables. - Integration with Azure Active Directory: Power BI uses Azure AD for authentication and authorization. Conditional access policies can be applied. - Integration with Microsoft 365 Compliance Center: Data loss prevention (DLP) policies can be applied to Power BI content.
Licensing Overview
Power BI Free: Limited to creating reports in Power BI Desktop and viewing reports only in My Workspace. Cannot share or view reports shared by others.
Power BI Pro: $10/user/month. Allows sharing, collaboration, and consuming content. Required for publishing to the service and viewing shared content.
Power BI Premium Per User (PPU): $20/user/month. Includes all Pro features plus larger data limits, AI capabilities, and more frequent refreshes.
Power BI Premium (capacity): Reserved capacity starting at ~$5,000/month. Allows unlimited users (free viewers) within the organization to consume content without individual Pro licenses.
Security and Governance
Power BI supports row-level security (RLS) to restrict data access for specific users. For example, a sales manager can only see data for their region. RLS is defined in Power BI Desktop using DAX filters and enforced in the service. Additionally, sensitivity labels from Microsoft 365 Information Protection can be applied to Power BI content. Audit logs track user activities like viewing reports, sharing, and exporting data.
Common Commands and Tools
While Power BI is primarily GUI-based, there are some PowerShell cmdlets for administration:
- Get-PowerBIDataset - lists datasets
- Set-PowerBIDataset - modify dataset settings
- New-PowerBIReport - deploy reports programmatically
Also, the Power BI REST API allows automation of tasks like refreshing datasets, managing workspaces, and embedding reports.
How Power BI Interacts with Other Technologies
Azure Analysis Services: Power BI can connect to Azure Analysis Services for enterprise-grade tabular models.
Azure Synapse Analytics: DirectQuery to Synapse for real-time analytics on large data.
Microsoft Flow (Power Automate): Trigger flows when a report is refreshed or when an alert is triggered.
Power Apps: Embed Power BI reports in canvas apps.
Dynamics 365: Many Dynamics 365 entities have Power BI content packs.
Performance Considerations
Import mode is best for small to medium datasets (under 1 GB) where near-real-time is not required. Refresh schedules can be set.
DirectQuery is for large datasets or real-time needs, but query performance depends on the source system.
Composite models allow mixing import and DirectQuery in the same model.
Aggregations can be used to pre-aggregate data for faster queries.
Exam Relevance
For MS-900, you need to know:
The difference between Power BI Free, Pro, and Premium.
That Power BI is part of the Microsoft Power Platform (along with Power Apps and Power Automate).
That Power BI can connect to various data sources, including Excel, SQL, and cloud services.
That Power BI reports can be shared via the Power BI Service, embedded in Teams or SharePoint, and accessed on mobile devices.
The concept of data gateways for on-premises data sources.
You do NOT need to know how to create DAX measures or complex data models; that is for the PL-900 or DA-100 exams.
Connect to Data Sources
In Power BI Desktop, click 'Get Data' to choose from hundreds of connectors. For example, select 'SQL Server' and enter the server and database. You can import data (default) or use DirectQuery. Import mode copies data into Power BI's in-memory engine (VertiPaq), which compresses and indexes data for fast queries. DirectQuery sends queries to the source database in real-time, suitable for large datasets but with slower performance. After selecting tables, you can apply transformations in Power Query Editor before loading.
Transform and Clean Data
Power Query Editor opens with a preview of your data. Each transformation step is recorded. Common steps: Remove columns, filter rows, split columns, merge queries, pivot/unpivot. For example, to combine sales and product tables, use Merge Queries on ProductID. Power Query uses M language behind the scenes. You can rename steps for clarity. After transformations, click 'Close & Apply' to load data into the data model.
Build the Data Model
In the Model view, you can create relationships between tables by dragging fields. For example, drag ProductID from Sales table to ProductID in Product table. You can set cardinality (many-to-one, one-to-one) and cross-filter direction. Create calculated columns using DAX (e.g., `Profit = Sales[Amount] - Sales[Cost]`). Measures are created in the Fields pane (e.g., `Total Sales = SUM(Sales[Amount])`). Measures are evaluated at query time and are key for aggregations.
Create Visualizations
Drag fields onto the report canvas to create visuals. For example, drag 'Category' to Axis and 'Total Sales' to Values to create a bar chart. Use the Visualizations pane to change chart type (column, line, pie, map, etc.). Add slicers for interactivity (e.g., a date slicer). You can format visuals (colors, labels, titles). Create multiple pages (tabs) for different aspects of the data. Test interactions: clicking a bar filters other visuals on the page by default.
Publish and Share
Save the .pbix file, then click 'Publish' to upload to the Power BI Service. Select a workspace (or create new). In the service, you can create dashboards by pinning visuals from reports. Share the dashboard or report with other users by entering their email addresses. They must have Power BI Pro licenses (unless content is in Premium capacity). You can also embed reports in SharePoint or Teams. Schedule data refresh in the dataset settings (requires gateway for on-premises sources).
Scenario 1: Sales Dashboard for a Retail Chain
A national retail chain uses Power BI to track sales performance across 500 stores. The data sources include an on-premises SQL Server for transactional sales data, an Azure SQL Database for inventory, and Excel files for store manager targets. A Power BI developer creates a data model with fact tables (Sales, Inventory) and dimension tables (Store, Product, Date). Measures calculate Year-over-Year growth, profit margins, and inventory turnover. The report is published to a Premium capacity workspace, allowing all store managers (free users) to view dashboards on their tablets via Power BI Mobile. A data gateway is installed on-premises to refresh the SQL Server data every 30 minutes. Common issues: gateway connectivity failures due to firewall changes, and slow report performance when too many visuals are on a single page. To mitigate, the team uses aggregated tables and limits visuals to 10 per page.
Scenario 2: Financial Reporting for a Multinational Corporation
A multinational corporation uses Power BI for financial consolidation across subsidiaries with different currencies and accounting standards. Data comes from Dynamics 365 Finance, Excel spreadsheets, and a cloud-based ERP. Power BI's built-in connector to Dynamics 365 pulls financial data. Currency conversion is handled via a DAX measure that uses exchange rate tables. Reports are shared with executives via Power BI Service, and they use natural language queries (Q&A) to ask questions like 'What was revenue in Q4?'. Sensitivity labels from Microsoft 365 Information Protection are applied to ensure only authorized users can view financial data. Row-level security restricts each subsidiary manager to their own data. The finance team uses Power BI Report Builder to create paginated monthly statements that are distributed via email subscriptions.
Scenario 3: Real-time Monitoring for a Logistics Company
A logistics company monitors delivery truck locations and package statuses using Power BI with DirectQuery to Azure SQL Database and streaming datasets from Azure IoT Hub. The report shows real-time map visuals with truck positions and ETA. Alerts are set up via Power Automate: if a delivery is delayed by more than 30 minutes, an email is sent to the dispatcher. The data refreshes every minute. Performance considerations: DirectQuery ensures data is current but requires optimized queries on the source database. Indexes are created on timestamp and location columns. The company uses Power BI Premium for larger data volumes (over 10 GB) and to allow external partners to view limited data via guest access in Azure AD.
What MS-900 Tests on Power BI
MS-900 objective 2.5 covers 'Describe the capabilities of analytics solutions in Microsoft 365'. Specifically, you need to:
Identify the components of Power BI (Desktop, Service, Mobile).
Understand licensing differences: Free vs. Pro vs. Premium.
Know that Power BI is part of the Power Platform.
Recognize integration points with Microsoft 365 (Teams, SharePoint, Excel).
Understand the concept of data gateways for on-premises data.
Common Wrong Answers and Why
'Power BI is a Microsoft 365 app like Word or Excel.' While Power BI is a Microsoft product, it is not part of the Office 365 suite included in E3/E5 licenses. It is a separate service with its own licensing. Candidates often confuse the Microsoft 365 admin center's ability to manage Power BI with it being a native app.
'Power BI Free allows sharing reports with anyone.' Power BI Free only allows you to create reports in Desktop and view your own content in My Workspace. Sharing requires Pro or Premium. Many candidates think Free is enough for collaboration because they confuse 'view' with 'share'.
'Power BI Premium is a per-user license.' Premium is a capacity-based license (reserved compute resources), not per-user. There is also Premium Per User (PPU), which is per-user, but the exam distinguishes between capacity and user licensing. Candidates often mix up Premium and PPU.
'Power BI can only connect to Microsoft sources.' Power BI has hundreds of connectors including Salesforce, Google Analytics, and many databases. The exam may list a specific non-Microsoft source and ask if Power BI can connect to it.
Specific Values and Terms
Refresh limits: Pro: 8/day, Premium: 48/day.
Data storage per user: Pro: 10 GB, Premium: 100 TB.
Dataset size limit: Pro: 1 GB, Premium: 10 GB (large dataset mode allows larger).
Gateway: On-premises data gateway (standard) vs. personal mode.
Workspace: Container for dashboards, reports, and datasets (like a SharePoint site).
Power BI Desktop: Free Windows app.
Edge Cases and Exceptions
Free users can view content if it's in a Premium capacity. This is a common exam scenario: 'Can a user without a Pro license view a report?' Answer: Yes, if the report is in a workspace assigned to Premium capacity.
Power BI Report Server is only available with Premium or SQL Server Enterprise with SA. Not with Pro.
Data gateways are not needed for cloud data sources (e.g., Azure SQL, Dynamics 365). Only for on-premises.
How to Eliminate Wrong Answers
If a question mentions 'sharing a report', look for Pro or Premium in the answer. If it says 'Free', eliminate it.
If a question asks about 'on-premises data refresh', the answer must include 'gateway'.
If a question asks about 'embedding in Teams', the answer is 'Power BI tab in Teams', not 'Power Automate'.
For licensing, remember: Pro = per user; Premium = capacity; PPU = per user with Premium features.
Power BI is a separate service from Microsoft 365; it requires its own license (Pro or Premium).
Power BI Free allows report creation in Desktop and personal viewing only; sharing requires Pro or Premium.
Power BI Premium is capacity-based, not per-user; it allows free users to view content.
Data gateways are required for on-premises data sources to enable scheduled refresh.
Power BI integrates with Microsoft Teams, SharePoint, and Excel for embedding and collaboration.
Power BI is part of the Microsoft Power Platform, along with Power Apps and Power Automate.
The maximum scheduled refreshes per day are 8 for Pro and 48 for Premium.
Row-level security (RLS) can restrict data access based on user roles.
Power BI Desktop is free and used for report creation; Power BI Service is cloud-based for sharing.
DirectQuery mode queries the source database in real-time; Import mode stores data in-memory.
These come up on the exam all the time. Here's how to tell them apart.
Power BI Pro
$10/user/month
8 scheduled refreshes per day
10 GB storage per user
1 GB dataset limit
Cannot use AI features (AutoML, Cognitive Services)
Power BI Premium Per User (PPU)
$20/user/month
48 scheduled refreshes per day
100 TB storage per user
10 GB dataset limit (large dataset mode available)
Includes AI features and advanced analytics
Power BI Free
Free to use
Cannot publish to Power BI Service
Cannot share reports with others
Can only view content in My Workspace
No data refresh scheduling
Power BI Pro
$10/user/month
Can publish to Power BI Service
Can share reports and dashboards
Can view content in shared workspaces
Up to 8 scheduled refreshes per day
Mistake
Power BI is included with Microsoft 365 E5 license.
Correct
Power BI is not included in any Microsoft 365 license. It requires a separate subscription: Power BI Pro ($10/user/month) or Power BI Premium (capacity-based). However, some Microsoft 365 licenses include limited Power BI capabilities? No, they do not. The only exception is that Power BI Desktop is free to download and use, but publishing to the service requires a license.
Mistake
Power BI can only connect to Microsoft data sources like Excel and SQL Server.
Correct
Power BI has hundreds of connectors including Salesforce, Google Analytics, SAP, Oracle, and many others. It also supports custom connectors via ODBC, REST APIs, and OData. The exam may test that Power BI is not limited to Microsoft sources.
Mistake
All Power BI users need a Pro license to view reports.
Correct
Users with a Power BI Free license can view reports if the content is hosted in a Premium capacity workspace. This is a key differentiator. However, Free users cannot create or share content in the service.
Mistake
Power BI reports can be printed directly from the service.
Correct
The Power BI Service does not have a built-in print feature for standard reports. For printing, you need to use Power BI Report Builder to create paginated reports, which are designed for print and PDF export. Standard interactive reports can be exported to PDF but may not format well.
Mistake
Power BI data refresh is real-time by default.
Correct
By default, Power BI imports data (Import mode) and requires a scheduled refresh to update. DirectQuery mode can provide near-real-time, but it queries the source on each interaction. Real-time streaming is possible with Power BI streaming datasets, but that is a separate feature for dashboards, not reports.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Power BI Pro is a per-user license ($10/user/month) that allows users to create, share, and consume reports. Power BI Premium is a capacity-based license (starting at ~$5,000/month) that reserves dedicated compute resources for your organization. With Premium, you can assign workspaces to the capacity, allowing users with Free licenses to view content without needing Pro. Premium also offers larger data limits (10 GB per dataset vs. 1 GB with Pro) and more frequent refreshes (48/day vs. 8/day). For the MS-900 exam, remember that Premium is about capacity, not per-user.
Yes, you can download and use Power BI Desktop for free to create reports. However, to publish those reports to the Power BI Service and share them with others, you need a Power BI Pro license (or have the content in a Premium capacity). Additionally, viewing shared reports in the service requires a Pro license unless the report is in a Premium workspace, in which case Free users can view it. So, limited use is free, but collaboration requires a license.
Power BI uses an on-premises data gateway to connect to on-premises data sources (e.g., SQL Server, Oracle, file systems). The gateway acts as a bridge between the Power BI Service and your local network. You install the gateway software on a server inside your network, register it with Power BI, and then configure data sources in the service. The gateway supports scheduled refresh and DirectQuery for on-premises sources. There are two modes: standard (for multiple users) and personal (for a single user).
A report is a multi-page collection of visualizations based on a single dataset. It can have multiple pages and interactive filters. A dashboard is a single-page canvas that contains tiles (visualizations) pinned from one or more reports or datasets. Dashboards can include tiles from multiple sources and can have natural language Q&A. Reports are created in Power BI Desktop; dashboards are created in the Power BI Service. For the exam, know that reports are the source, and dashboards are a curated view.
Yes, Power BI has mobile apps for iOS, Android, and Windows. These apps allow users to view dashboards and reports, interact with visuals, and receive alerts. The mobile app supports touch gestures and can be used offline with cached data. However, creating reports is not possible on mobile; that requires Power BI Desktop.
A workspace is a container in the Power BI Service that holds dashboards, reports, datasets, and dataflows. Workspaces are used for collaboration; you can assign roles (Admin, Member, Contributor, Viewer) to control access. Workspaces can be assigned to a Premium capacity to allow Free users to view content. There is also a personal workspace called 'My Workspace' for individual content.
Yes, Power BI has hundreds of built-in connectors for popular services like Salesforce, Google Analytics, Mailchimp, GitHub, and many databases (PostgreSQL, MySQL, etc.). It also supports generic connectors via ODBC, OData, and REST APIs. This makes Power BI a versatile tool for integrating data from diverse sources.
You've just covered Power BI in Microsoft 365 — now see how well it sticks with free MS-900 practice questions. Full explanations included, no account needed.
Done with this chapter?