Lesson 2: Connect to Data Sources and Shared Semantic Models

Why this matters

Every Power BI report starts with Get data. Pick the wrong source or the wrong kind of connection and you may spend weeks rebuilding a model that already exists. You could also end up with a report whose numbers don't match Finance's. The PL-300 study guide lists this as the first skill under Prepare the data → Get or connect to data: "Identify and connect to data sources or a shared semantic model."

Reminder from Level 1: this course is independent of Microsoft and can't promise you a pass. The official study guide is free on Microsoft Learn. The exam lasts 100 minutes and you need a scaled score of 700 to pass.

There are two broad ways to get data into a report:

1. Connect to a source (an Excel file, a folder, a database, Dataverse, a Fabric lakehouse or warehouse). You build your own model in Power Query.
2. Connect to a shared semantic model that someone has already built and published. You reuse their model through a live connection.

Take an Excel comparison. The first way is like importing raw sheets and building your own lookup tables. The second is like linking to the master workbook that Finance looks after.

Walkthrough 1: An Excel file

In Power BI Desktop, go to Home → Get data → Excel workbook, pick Budget.xlsx, tick the Budget sheet in the Navigator, then choose Transform Data. In Power Query, open Home → Advanced Editor:

let
    // Open the workbook file and list everything inside it (sheets, tables, named ranges)
    Source = Excel.Workbook(File.Contents("C:\Data\Budget.xlsx"), null, true),
    // Pick the item named Budget whose Kind is Sheet, then take its Data
    Budget_Sheet = Source{[Item="Budget", Kind="Sheet"]}[Data],
    // Use the first row as column headers
    #"Promoted Headers" = Table.PromoteHeaders(Budget_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Gotcha: if your data is formatted as an Excel table, connect to the table (Kind="Table") and not the sheet. A table grows cleanly when rows are added. A sheet brings in stray cells and blank rows.

Walkthrough 2: A folder of CSVs

Say Sales drops a new file each month (2026-01.csv, 2026-02.csv and so on). Go to Get data → Folder, enter the path and choose Combine & Transform Data:

let
    // Lists every file in the folder AND its subfolders, with a Content (binary) column
    Source = Folder.Files("C:\Data\MonthlySales"),
    // Keep only CSVs, in case someone saves a stray .xlsx in the folder
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv")
    // ...Power Query then adds helper queries (Sample File, Transform File)
    // and runs Transform File on each file's Content before appending the results
}

Power Query works out how to process every file from the example (sample) file you choose. Then it applies the same steps to all of them. Next month's file gets picked up automatically on refresh.

Gotcha: all the files must share the same format and column layout. If one file has a renamed column, the combine step breaks or quietly fills that column with nulls.

Walkthrough 3: A SQL database

Go to Get data → SQL Server database, enter the server srv and the database db, then pick dbo.Sales:

let
    // Connect to server "srv", database "db"
    Source = Sql.Database("srv", "db"),
    // Navigate to schema dbo, table Sales
    dbo_Sales = Source{[Schema="dbo", Item="Sales"]}[Data]
in
    dbo_Sales

The dialog also asks you to choose Import or DirectQuery. Leave it on Import for now, because Level 4 covers that decision. Reading this M is an exam skill in its own right. The first step (Source) always tells you which connector and which source a query uses.

Other sources you should recognise

• Dataverse is the data store behind Power Apps and Dynamics 365. Go to Get data → Dataverse and sign in. You'll see a list of environments, and each one has a URL like https://yourenv.crm.dynamics.com. Pick the tables you need.
• Fabric lakehouses and warehouses are found through the OneLake catalog in Power BI Desktop. Select the lakehouse or warehouse and choose Connect. The Connect drop-down also offers Connect to SQL endpoint, which lets you query the item's SQL analytics endpoint like a database. The storage mode choices behind this belong to Level 4.

Walkthrough 4: A shared semantic model (live connection)

1. Home → Get data → Power BI semantic models.
2. The OneLake catalog opens. It lists the semantic models you have Build permission on. You can filter to Endorsed in your org to find promoted or certified models.
3. Select a model and choose Connect. The Data pane fills with the published tables and measures.

What a live connection lets you do:

• Build visuals and whole reports on a vetted model that is already refreshed
• Add report measures, calculated columns, visual calculations and calculated tables
• Inherit the model's row-level security (RLS). The model applies RLS to you as well

What it does not let you do:

• Open Power Query. Transform data is disabled
• Change the model itself, for example by adding or editing source tables
• Mix in another source directly. To do that you'd select Make changes to this model, which turns the connection into DirectQuery and creates a composite model. That's a deliberate step with its own trade-offs.

Gotcha: without Build permission on the model, it won't appear in the list at all. Being able to view a report built on it isn't enough.

Choosing well

From experience: reuse before you rebuild. A second copy of the Sales model will drift away from the original, and someone will ask why your revenue figure differs from Finance's.

What the exam asks about this

Expect scenario questions such as "users need to create their own reports while making sure numbers match the certified model." The answer is a live connection to the shared semantic model, and you need Build permission. Expect questions on what a live connection blocks (Power Query, changes to the model) and allows (report measures). You may also be asked to pick the connector from a short description, or to read M such as Sql.Database(...) or Folder.Files(...).

Key takeaways

• The first M step (Source = ...) shows the connector: Excel.Workbook, Folder.Files, Sql.Database.
• The Folder connector combines files of the same structure by using one sample file's steps.
• Fabric lakehouses, warehouses and shared semantic models are all found through the OneLake catalog.
• A live connection needs Build permission. It allows report measures, but not Power Query or changes to the model.
• Choose a shared semantic model when consistency and reuse matter more than control.