This chapter covers Power BI fundamentals as tested in DP-900 objective 3.4. Power BI is Microsoft's premier business analytics service, enabling interactive visualizations and self-service analytics. Approximately 15-20% of DP-900 exam questions touch Power BI capabilities, components, and lifecycle. Mastering this chapter will prepare you to answer questions about Power BI Desktop, Service, mobile apps, and the end-to-end workflow from data connection to dashboard sharing.
Jump to a section
Imagine a busy restaurant kitchen where orders come in, ingredients are prepped, and dishes are plated. Power BI Desktop is the chef's prep station: raw ingredients (data from Excel, SQL Server, Azure) are cleaned, chopped, and combined using Power Query (the sous chef). The chef then builds a recipe (data model) with relationships and calculations (DAX). The finished dish is a report with visuals (bar charts, maps). The chef publishes the recipe to the restaurant's central kitchen (Power BI Service), where waiters (business users) can view the menu (dashboards) on tablets (Power BI mobile apps). The head chef (admin) manages access and schedules automatic refreshes (gateway) so the menu always shows today's specials. If a waiter wants to ask a question like 'Which dish sold most yesterday?', they use natural language (Q&A) and the kitchen answers by querying the model. The entire process—from raw ingredient to served dish—mirrors Power BI's flow: connect, transform, model, visualize, share, and collaborate.
What is Power BI?
Power BI is a suite of business analytics tools from Microsoft that allows users to connect to hundreds of data sources, transform and clean data, create interactive visualizations, and share insights across an organization. It comprises three main components: - Power BI Desktop: A free Windows application for building reports and data models. - Power BI Service: A SaaS (Software as a Service) platform for publishing, sharing, and collaborating on reports and dashboards. - Power BI Mobile Apps: Native apps for iOS, Android, and Windows devices for consuming reports on the go.
Why Power BI Exists
Organizations accumulate vast amounts of data but struggle to extract actionable insights. Traditional reporting tools require IT intervention for every new report. Power BI empowers business analysts to create their own reports using a familiar Excel-like interface, while IT maintains governance through data gateways, row-level security, and usage monitoring. The exam tests your understanding of this self-service model and how each component fits.
Core Components in Detail
#### Power BI Desktop
- Purpose: Authoring environment for creating reports.
- Key Features:
- Power Query Editor: For data transformation (ETL: Extract, Transform, Load). Supports over 150 connectors.
- Data Model: A tabular model using in-memory columnar storage (VertiPaq). Relationships, hierarchies, and measures are defined here.
- DAX (Data Analysis Expressions): Formula language for calculated columns, measures, and tables. Examples: SUM, CALCULATE, FILTER.
- Visualizations: Built-in and custom visuals from AppSource. Common visuals: bar chart, line chart, map, table, matrix, card, slicer.
- Report Pages: A report can have multiple pages, each with its own set of visuals.
#### Power BI Service - Purpose: Cloud-based sharing and collaboration. - Key Concepts: - Workspaces: Containers for reports, dashboards, datasets, and dataflows. Each workspace has access control. - Dashboards: Single-page canvas with pinned visualizations (tiles) from one or more reports. Dashboards are read-only and auto-refresh. - Datasets: Published data models from Desktop or created directly in the service via dataflows or streaming datasets. - Dataflows: Reusable ETL logic stored in the service, built on Power Query Online. - Gateways: On-premises data gateway for refreshing data from on-premises sources (e.g., SQL Server). Modes: Standard (enterprise) and Personal. - Scheduled Refresh: Configured in dataset settings. Default frequency: 8 times per day for shared capacity, 48 times for Premium. - Usage Metrics: Track report and dashboard views, unique viewers, and sharing activity.
#### Power BI Mobile Apps - Purpose: Access reports and dashboards on mobile devices. - Features:
Touch-optimized interaction.
Annotations and sharing via screenshot.
QR code scanning to open specific reports.
Alerts and push notifications.
How Power BI Works Internally — Step Through the Mechanism
Connect to Data: In Power BI Desktop, you choose a data source (e.g., SQL Server, Excel, Azure Blob Storage). The connection string, credentials, and query are stored in the .pbix file.
Transform Data: Power Query Editor loads a preview. You apply transformations (e.g., remove columns, filter rows, merge queries). These steps are recorded as M language formulas.
Load to Model: Transformed data is loaded into the in-memory columnar engine (VertiPaq). Relationships are created between tables (one-to-many, many-to-many).
Build Visuals: Drag fields onto the canvas. DAX measures are evaluated on the fly using the filter context. The visual sends a query (in DAX or MDX) to the engine, which returns aggregated results.
Publish to Service: Click Publish. The .pbix file is uploaded to a workspace. The dataset, report, and any dashboards are created.
Configure Refresh: In the service, you set up a gateway and schedule refresh if the data source is on-premises or requires credentials.
Share and Collaborate: Share reports/dashboards with users or groups. Row-level security (RLS) can be applied to restrict data access.
Key Components, Values, Defaults, and Timers
Default Refresh Frequency: Shared capacity: 8 times per day (every 180 minutes). Premium capacity: 48 times per day (every 30 minutes).
Gateway: On-premises data gateway supports up to 10 data sources per gateway. Personal gateway is for single-user scenarios and not recommended for production.
Dataset Size Limits: Shared capacity: 1 GB per dataset. Premium: up to 400 GB depending on SKU.
Row-Level Security (RLS): Defined using DAX filters. Roles are created in Desktop and applied after publish.
Power BI Desktop Save Format: .pbix file (Power BI eXchange). Can also be saved as .pbip (Power BI Project) for source control integration.
Service Tiers: Free (user only), Pro ($10/user/month), Premium Per User ($20/user/month), Premium capacity (starting at $4,995/month for P1).
Configuration and Verification Commands
While Power BI is primarily GUI-based, some administrative tasks use PowerShell or REST APIs.
- PowerShell Cmdlets: Get-PowerBIWorkspace, New-PowerBIReport, Set-PowerBIDataset.
- REST API: https://api.powerbi.com/v1.0/myorg/datasets.
- Gateway Verification: In gateway settings, test connection for each data source.
- Refresh History: In dataset settings, view refresh history to see success/failure and duration.
How Power BI Interacts with Related Technologies
Azure Synapse Analytics: DirectQuery mode allows querying Synapse without importing data.
Azure Analysis Services: Power BI can connect to AAS models (live connection) for enterprise-grade semantic models.
Excel: Power BI can export data to Excel, and Excel can use Power BI datasets via Analyze in Excel.
Microsoft Teams: Reports can be embedded in Teams tabs.
Power Automate: Alerts from Power BI can trigger flows (e.g., send email when a metric exceeds threshold).
Azure Machine Learning: Power BI can consume ML models for predictions (e.g., binary classification using Azure ML endpoint).
Exam-Relevant Details
The DP-900 exam expects you to know the purpose of each component, not deep configuration.
Common wrong answer: Confusing Power BI Desktop with Power BI Service roles. Example: Thinking you create dashboards in Desktop — you create reports there; dashboards are created in Service.
Another trap: Believing that Power BI Desktop can publish directly to mobile apps — it publishes to Service, then mobile app connects to Service.
Key terms: Dataset, Report, Dashboard, Workspace, Gateway, Dataflow, RLS, DAX, Power Query, M language.
Numbers: Default refresh 8 times/day (shared), 48 (premium). Dataset size limit 1 GB (shared).
Connect to a Data Source
Open Power BI Desktop and click 'Get Data'. Choose from hundreds of connectors: Azure SQL Database, Excel, CSV, SQL Server, SharePoint, etc. Enter server name, database, and authentication method (Windows, SQL, OAuth). For cloud sources, you may need to sign in with organizational account. The Navigator window shows available tables/views. Select one or more, then click 'Load' to import or 'Transform Data' to open Power Query Editor.
Transform Data with Power Query
In Power Query Editor, each step is recorded as an M expression. Common transformations: Remove columns, filter rows, split column by delimiter, merge queries (like SQL JOIN), unpivot columns, change data type, add custom column. The Applied Steps pane on the right shows the sequence. You can reorder, delete, or modify steps. Once done, click 'Close & Apply' to load data into the model.
Build the Data Model
In the Model view, drag fields between tables to create relationships. Default cardinality: one-to-many. Cross-filter direction: single (from dimension to fact) or both. Create measures using DAX: e.g., `Total Sales = SUM(Sales[Amount])`. Create calculated columns: e.g., `Year = YEAR(Sales[Date])`. Hierarchies can be defined (e.g., Year > Quarter > Month). Ensure relationships use proper keys and avoid circular dependencies.
Create Visuals and Reports
In Report view, select a visual type from the Visualizations pane (e.g., stacked bar chart, line chart, map). Drag fields into well-defined areas: Axis, Legend, Values, Tooltips. Use filters (visual, page, report level) to limit data. Format visuals using the Format pane (colors, labels, borders). Add slicers for interactive filtering. Create multiple report pages and add page navigation buttons.
Publish to Power BI Service
Click 'Publish' on the Home tab. Select a workspace (My Workspace or a shared workspace). The .pbix file is uploaded. The report appears under 'Reports', the dataset under 'Datasets'. You can then create a dashboard by pinning visuals from the report. In the service, configure scheduled refresh: go to dataset settings, enable scheduled refresh, specify frequency and time zone, and set up gateway connection if needed.
Enterprise Scenario 1: Sales Performance Dashboard
A retail company with 500 stores uses Power BI to track daily sales. Data sources: Azure SQL Database for transactional data, Excel files for store targets. The BI team builds a report in Desktop with measures like Total Sales, Sales vs Target, YoY Growth. They publish to a shared workspace and set up scheduled refresh every 30 minutes (Premium capacity). Regional managers access dashboards on iPads via Power BI Mobile. Row-level security restricts each manager to see only their region's data. Common issue: Refresh fails if the gateway is offline or credentials expire. Solution: Use a standard gateway with a service account and monitor refresh history.
Scenario 2: Real-Time Monitoring with Streaming Datasets
A logistics company monitors truck locations. GPS data streams into Azure Event Hubs. Power BI uses a streaming dataset (push dataset) to create a real-time dashboard showing truck positions on a map and average speed. The dataset is created in the service via API. The dashboard auto-refreshes every second. No Desktop involvement. Pitfall: Streaming datasets have limited storage (15 minutes of data). For historical analysis, the data must be also imported into a regular dataset.
Scenario 3: Self-Service Analytics for Marketing
Marketing analysts need to combine web analytics (Google Analytics) with CRM data (Dynamics 365). They use Power BI Desktop to import both sources, merge by customer ID, and create a customer journey report. They publish to a workspace shared with the marketing team. The team uses Q&A (natural language) to ask 'Show me conversion rate by channel'. The Q&A engine interprets the question and generates a visual. Problem: If the dataset lacks proper synonyms or measures, Q&A fails. Solution: Predefine Q&A suggestions in Desktop.
Performance Considerations
Import mode is fastest for interactive queries but requires refresh. DirectQuery avoids refresh but impacts source database performance.
Composite models (mix of import and DirectQuery) are supported in Premium.
Large datasets (>1 GB) require Premium capacity or use of aggregations.
Gateway should be installed on a dedicated server with sufficient RAM and network bandwidth.
DP-900 Objective 3.4: Describe Power BI Capabilities
The exam tests your ability to:
Identify the correct tool for a given task (Desktop vs Service vs Mobile).
Understand the publishing workflow: Desktop -> Service -> Share.
Recognize the difference between a report and a dashboard.
Know the purpose of a gateway.
Identify data refresh options and limitations.
Understand row-level security basics.
Common Wrong Answers and Why Candidates Choose Them
'Power BI Desktop is used to create dashboards.' The exam says reports are created in Desktop; dashboards are created in Service by pinning visuals. Candidates confuse the two because both display visuals.
'Power BI Service can transform data using Power Query.' While the service has dataflows (Power Query Online), the exam often contrasts Desktop (for transformation) vs Service (for sharing). The trap is that the service can transform via dataflows, but the primary transformation tool is Desktop.
'Scheduled refresh is configured in Power BI Desktop.' It is configured in the Service after publish. Desktop only has a Preview feature for refresh, but the exam expects Service for scheduling.
'Power BI Mobile can edit reports.' Mobile apps are read-only for reports. Editing is done in Desktop or Service (if you have edit permissions).
'Free license can share reports.' Free users can only consume content in My Workspace or if the workspace is in Premium capacity. Sharing requires Pro license.
Specific Numbers and Terms on the Exam
Default refresh: 8 times/day for shared, 48 for Premium.
Dataset size limit: 1 GB for shared.
Gateway types: Standard (enterprise) and Personal.
License tiers: Free, Pro, Premium Per User, Premium capacity.
Components: Desktop, Service, Mobile, Gateway, Dataflows, Datasets.
Edge Cases
DirectQuery vs Import: Exam may ask which mode is best for real-time data but with low latency requirements. DirectQuery is real-time but slower; Import is fast but not real-time.
Composite models: Not tested in detail, but know they exist.
Power BI Report Server: An on-premises alternative to Service, but rarely tested in DP-900.
How to Eliminate Wrong Answers
If the question mentions 'creating a report', the answer is Desktop.
If it mentions 'sharing with others', the answer is Service.
If it mentions 'mobile access', the answer is Mobile apps.
If it mentions 'scheduled refresh', the answer is Service (dataset settings).
If it mentions 'data transformation', the answer is Desktop (Power Query).
Power BI has three main components: Desktop (authoring), Service (sharing), Mobile (consumption).
Reports are created in Desktop; dashboards are created in Service from pinned visuals.
Data transformation (ETL) is done in Power Query Editor within Desktop.
Scheduled refresh is configured in the Service, not Desktop.
Default refresh frequency: 8 times/day for shared capacity, 48 times/day for Premium.
On-premises data gateway is required to refresh data from on-premises sources.
Row-level security (RLS) is defined in Desktop and applied after publish.
These come up on the exam all the time. Here's how to tell them apart.
Power BI Desktop
Free Windows application for authoring reports and data models.
Contains Power Query Editor for data transformation.
Supports creating measures, calculated columns, and relationships using DAX.
Output is a .pbix file that can be published to the Service.
Cannot be used for sharing or collaboration directly.
Power BI Service
Cloud-based SaaS for publishing, sharing, and managing reports and dashboards.
Does not include Power Query Editor for datasets (except dataflows).
Supports dashboard creation by pinning visuals from reports.
Manages dataset refresh schedules, gateways, and row-level security.
Requires Pro or Premium license for sharing content.
Mistake
Power BI Desktop and Power BI Service are the same product with different interfaces.
Correct
They are distinct: Desktop is a free Windows app for authoring reports; Service is a cloud SaaS for sharing and collaboration. You cannot create dashboards in Desktop, nor can you edit data models in Service (except dataflows).
Mistake
You need a Power BI Pro license to use Power BI Desktop.
Correct
Power BI Desktop is completely free. A Pro license is required to publish reports to the service and share content with others. You can use Desktop without any license.
Mistake
Power BI can only connect to Microsoft data sources.
Correct
Power BI has over 150 connectors, including non-Microsoft sources like Google Analytics, Salesforce, Oracle, MySQL, and many more via generic ODBC/OData.
Mistake
Scheduled refresh is configured in Power BI Desktop.
Correct
Scheduled refresh is configured in the Power BI Service after the report is published. Desktop does not support scheduling; it only allows manual refresh.
Mistake
Dashboards and reports are the same thing.
Correct
A report is a multi-page document with interactive visuals based on a single dataset. A dashboard is a single-page canvas with pinned visuals (tiles) that can come from multiple reports or datasets. Dashboards are read-only and cannot be edited in the same way as reports.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Power BI Desktop is a free Windows application for building reports and data models. Power BI Service is a cloud platform for publishing, sharing, and collaborating on reports and dashboards. Desktop is used for authoring; Service is used for distribution. You cannot create dashboards in Desktop, and you cannot edit data models in Service (except via dataflows).
Publish the report from Desktop to a workspace in Power BI Service. Then, in the Service, you can share the report or dashboard directly with users or groups. You can also publish to a broader audience by embedding in a SharePoint site or Teams. Sharing requires a Power BI Pro license for both the sharer and the recipient, unless the workspace is in Premium capacity.
An on-premises data gateway acts as a bridge between Power BI Service and on-premises data sources (e.g., SQL Server, file shares). You need a gateway if your dataset refreshes require accessing data that is not in the cloud. The gateway securely transfers data without opening inbound ports. There are two types: Standard (enterprise, shared) and Personal (single user).
Yes. Power BI supports real-time scenarios using streaming datasets (push datasets) or DirectQuery. Streaming datasets can be pushed via REST API and auto-refresh dashboards every second. DirectQuery queries the source database in real-time but may be slower. For near-real-time, scheduled refresh can run as often as every 30 minutes (Premium).
A report is a multi-page document with interactive visuals based on a single dataset. You can filter, slice, and cross-highlight visuals. A dashboard is a single-page canvas with pinned visuals (tiles) that can come from multiple reports or datasets. Dashboards are read-only and cannot be edited; they are designed for at-a-glance monitoring.
After publishing a report, go to the dataset settings in Power BI Service. Under 'Scheduled refresh', toggle it on, set the frequency (e.g., daily, weekly) and time slots. You must have a gateway configured for on-premises sources. The maximum refresh frequency depends on your license: 8 times/day for shared, 48 times/day for Premium.
RLS restricts data access at the row level based on user identity. You define roles in Power BI Desktop using DAX filters (e.g., 'Salesperson = USERPRINCIPALNAME()'). After publishing, you assign users to roles in the Service. RLS applies to reports and dashboards, ensuring users only see data relevant to them.
You've just covered Power BI Fundamentals — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?