Getting and Connecting to Data

Introduction

This lesson opens the domain Prepare the data, which carries 25–30% of the PL-300 skills measured (skills outline dated April 20, 2026). Every Power BI solution starts with a decision that is easy to make badly: where does the data come from, how does Power BI reach it, and is it copied into the model or queried live? Microsoft publishes the weight ranges, the 700/1000 scaled passing score and the 100-minute exam time, but it does not publish the question count or the mix of question types. OnLevelUp mock exams therefore use a 50-question practice length and an 80% practice target; neither figure is official, and 80% is not a conversion of 700/1000.

After this lesson you will be able to choose and connect to data sources, reuse shared semantic models, configure credentials and privacy levels, pick between Import, DirectQuery and Direct Lake, and use parameters to make a solution portable between environments.

Learning objectives

• Identify the right connector and connection pattern for files, databases, online services and existing Power BI content.
• Explain what a shared semantic model is and when to connect to one instead of re-importing data.
• Configure data source credentials and privacy levels and predict their effect on query folding and data combination.
• Choose between Import, DirectQuery and Direct Lake storage modes from stated requirements.
• Create and use parameters to change a source, a filter value or a folder path.

1. Where the data comes from

Power BI Desktop uses Get data to open connectors. Each connector hands a query to Power Query, the transformation engine covered in the next two lessons. The exam expects you to recognise the main families:

Example. Fabrikam Retail receives a monthly CSV export from each of 40 stores in a SharePoint document library. Instead of importing 40 files by hand, the analyst uses the SharePoint Folder connector, filters to the current year, and uses Combine files so one transform is applied to every file. When store 41 opens, dropping its CSV into the library is enough; the next refresh includes it.

Exam tip: when the stem says "files with the same structure will be added every month", think Folder + Combine. When it says "the data already exists in a certified model owned by the data team", think connect to the semantic model, not "import the underlying database again".

2. Shared semantic models and dataflows

A semantic model is the published data model in the Power BI service: tables, relationships, measures, security. Many reports can connect live to the same model. This is the hub-and-spoke pattern: one team curates the model, report authors build thin reports on top of it.

How to connect: in Desktop choose Get data → Power BI semantic models (or the OneLake catalog), pick the model, and choose Connect. You can then build visuals and report-level measures. Things to know:

• You need Build permission on the semantic model to create new content from it. Read permission only lets you consume existing reports.
• The connection is live; the model's logic and security (row-level security) apply, and the data is not copied into your file.
• You can convert the connection into a DirectQuery connection to the semantic model to add your own tables and relationships (a composite model). This enables mixing the shared model with a local Excel file, at the cost of more complexity and DirectQuery behaviour.
• Dataflows (Gen1 and Gen2) centralise Power Query logic so that several models reuse the same cleaned tables. A dataflow prepares data; a semantic model analyses it.

Common trap: importing a shared model's data into a new local model duplicates logic, breaks the single source of truth, and makes security inconsistent. If the scenario stresses consistency and governance, reuse the shared model.

3. Data source settings, credentials and privacy levels

Data source settings

In Desktop, Transform data → Data source settings lists every source used by the file. Here you can change the source (for example when a server is renamed), edit permissions (credentials) and clear them. In the service, the same information appears under the semantic model's Settings → Data source credentials.

Credentials

Each source has an authentication method: Windows, Database (username and password), Organizational account (Microsoft Entra ID / OAuth), Anonymous, or Basic/API key for web sources. The credentials are stored per user in Desktop and per semantic model in the service. A refresh fails with credentials invalid when the password expires, the OAuth token is revoked or the owner leaves the company. A common governance fix is to use a service principal or a managed identity where the source supports it, so refresh does not depend on one person's account.

For on-premises sources the service needs a gateway to reach the network. The data source must also be added to the gateway with matching server and database names; a mismatch is a classic cause of refresh failure.

Privacy levels

Privacy levels tell Power Query how isolated each source is when it combines data from different sources in one query (for example merging a SQL table with an Excel file):

The firewall exists to prevent data leaking from one source into another: if the engine pushes values from a Private SQL table into a query against a web service (for example to filter it), those values would be sent to the web host. To avoid that, Power Query buffers or evaluates sources separately, which can disable query folding and slow refresh. Setting the right levels (for instance both internal sources as Organizational) lets the engine combine them efficiently.

Fast Combine, the option to ignore privacy levels, is a Desktop-only convenience; the service ignores it, so do not rely on it. The typical error "Formula.Firewall: Query references other queries or steps, so it may not directly access a data source" is resolved by setting privacy levels consistently or by restructuring queries so that data access and combination happen in separate queries.

Worked example 1: a firewall error. Scenario. An analyst merges a Sales table from Azure SQL Database (internal) with ExchangeRates from a public central-bank CSV on the web. In Desktop it works with Fast Combine on. After publishing, scheduled refresh fails with a firewall error. How should it be fixed without exposing sales data to the internet?

Reasoning.

1. The service ignores Fast Combine, so privacy levels now matter.
2. Sales is internal: set it to Organizational (or Private if very sensitive).
3. The central-bank CSV is public: set it to Public.
4. With Organizational and Public, data from the Public source may flow into the Organizational query, but not the reverse, so no sales values are sent to the web host.
5. Better still, load the rates in a separate query and merge locally, so the merge is evaluated inside the engine after both sources are retrieved.

Why other options are worse. Marking both as Public removes protection and may breach policy. Disabling privacy firewall checks is not available in the service. Importing rates manually into Excel creates a stale copy.

4. Import, DirectQuery and Direct Lake

Storage mode defines whether data is copied into the model or queried when the user interacts with a visual.

Key points:

• Import is the default and best-performing choice. Choose it unless a requirement forces something else.
• DirectQuery keeps data at the source. Choose it when the data is too large to import, must be up to date at each interaction, or governance forbids copying. Each visual sends one or more queries to the source, so a slow source gives slow reports, and some Power Query transformations and DAX functions are unavailable or limited.
• Direct Lake reads Delta/Parquet files in OneLake directly, without importing them and without translating queries to SQL. It requires data in a Fabric lakehouse or warehouse, and it fits when you want Import-like speed with fresh data. If a query cannot be served from Direct Lake (for example because of certain unsupported features or capacity guardrails), a Direct Lake on SQL endpoint model can fall back to DirectQuery; a Direct Lake on OneLake model does not fall back.
• Dual mode lets a table behave as Import or DirectQuery depending on the query; it is useful for dimension tables in a composite model so they can be joined to either storage mode efficiently.
• A composite model mixes storage modes in one model.

Worked example 2: choosing a storage mode. Scenario. Northwind Logistics stores 8 billion shipment events in a Fabric lakehouse. Dashboards must show events within minutes of arrival, and users complain that the previous DirectQuery model against a SQL warehouse is slow. What should the analyst do?

Reasoning.

1. The data is already in a Fabric lakehouse as Delta tables.
2. Near-real-time freshness rules out a scheduled-refresh Import model.
3. Size (8 billion rows) makes a full Import copy impractical.
4. Direct Lake gives memory-speed scans over Delta tables with fresh data.
5. Choose a Direct Lake semantic model on the lakehouse tables; model relationships and measures as usual.

Why not the others. Import: data is stale between refreshes and may exceed size limits. DirectQuery: already tried, slow because each visual translates to SQL. Dual: only affects dimensions in composite models.

Exam tip: read for the discriminating requirement. "Real-time + huge + Fabric" → Direct Lake. "Real-time + non-Fabric relational source" → DirectQuery. "Fastest and complete DAX" → Import.

5. Parameters

A Power Query parameter is a named value used in queries. Create it in Transform data → Manage parameters → New parameter, choosing a name, type (Text, Decimal number, Date…), whether it is required, and optionally a list of suggested or allowed values.

Typical uses:

• Switch environments: ServerName and DatabaseName parameters let you move from Dev to Prod by changing the parameter value in the service (semantic model settings → Parameters) without republishing.
• Dynamic file paths: a FolderPath parameter replaces a hard-coded path.
• Filtering to limit data: a RowLimit or StartDate parameter keeps the Desktop file small during development and is changed in the service for full loads.
• Incremental refresh: the reserved RangeStart and RangeEnd Date/Time parameters define the refresh window; incremental refresh is configured on a table that filters its date column with these parameters.

M example using parameters:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Sales = Source{[Schema = "dbo", Item = "FactSales"]}[Data],
    Filtered = Table.SelectRows(Sales, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    Filtered

Note >= on one boundary and < on the other; using <= on both would load boundary rows twice in adjacent partitions.

Changing a parameter value in the service requires being the semantic model owner and, for the parameter to appear, the parameter must be used by the query and not be of the unsupported "Any"/Binary type.

6. Pitfalls and exam tips

• A hard-coded local path (C:\Users...) will fail in the service; use SharePoint/OneDrive or a gateway with a shared path.
• DirectQuery with several slow visuals per page multiplies source queries; reduce visuals, use aggregations, or switch to Import.
• Updating a source server name means editing Data source settings or a parameter, not rewriting every query.
• Privacy levels are per data source, not per query, and are saved in the file but must be reviewed in the service for cloud sources.
• Semantic model live connections cannot be edited in Power Query: the transformations belong in the source model.
• Exam tip: distractors often mention "increase the refresh frequency" for a freshness requirement; remember scheduled refresh has limits (8 times a day on shared capacity, up to 48 on Premium/Fabric capacity), while DirectQuery or Direct Lake removes the wait.

7. Mini case study

Case. Adventure Works Bikes has three data sources: an on-premises SQL Server (orders), an Excel workbook on SharePoint (targets, edited monthly by the finance team), and a certified Power BI semantic model owned by HR (headcount). A new sales report must show orders versus targets, and regional managers must also see headcount per region. Performance must be good and HR must keep control of headcount logic and security.

Questions. (1) How do you connect each source? (2) What storage modes? (3) What must be configured for refresh?

Solution.

1. Orders: SQL Server connector in Import mode (best speed; data refreshes nightly).
2. Targets: SharePoint Online file connector in Import mode with an Organizational account; because targets change monthly, scheduled refresh picks up edits.
3. Headcount: connect to the HR semantic model and add it as a DirectQuery-to-semantic-model source in a composite model, keeping HR's measures and RLS. You need Build permission.
4. Refresh: install an on-premises data gateway, add the SQL source with identical server/database names, and set privacy levels to Organizational for the SQL and SharePoint sources so they can be combined without firewall errors.
5. A parameter ServerName allows switching between test and production servers.

Key terms

• Connector: component that reads a type of source.
• Semantic model: published data model with relationships, measures and security.
• Privacy level: classification (Private, Organizational, Public) controlling data sharing between sources.
• Import / DirectQuery / Direct Lake: storage modes.
• Composite model: model mixing storage modes or sources.
• Gateway: bridge to on-premises data.
• Parameter: reusable named value in Power Query.

Summary checklist

• I can pick Folder + Combine for many same-shaped files.
• I reuse shared semantic models and know Build permission is required.
• I can fix credential and firewall errors with data source settings and privacy levels.
• I can choose Import, DirectQuery or Direct Lake from the requirements.
• I can create parameters and use RangeStart/RangeEnd for incremental refresh.

Official sources

• Study guide for Exam PL-300: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/pl-300 (opens in a new tab)
• Privacy levels in Power Query: https://learn.microsoft.com/en-us/power-query/privacy-levels (opens in a new tab)
• Direct Lake overview: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview (opens in a new tab)
• DirectQuery in Power BI: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about (opens in a new tab)