Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 56 ยท Getting Data

Connecting to Data Sources

Learn how Get Data works in Power BI: files, folders, databases, web and online services, the Navigator, and what a connector query looks like in M.

A report is only as good as the data behind it, and Power BI can read from an unusually wide range of places: spreadsheets, text files, folders full of exports, databases, web pages, cloud services and APIs. Each kind of source is reached through a connector, a component that knows how to talk to that source and hand rows to Power Query.

This lesson explains how Get data works, the main families of connectors and what each one is best at, and what the connection looks like behind the scenes as a line of M code. The Power BI parts are read-only descriptions and code, since the real connectors cannot run in a browser. The Python stand-ins show how the same kinds of source (CSV, JSON, a folder of files) are read with pandas, so you can see what each connection produces.

How Get data works

Four steps to any source

Pick a connector Home, Get data, choose the source type.
Authenticate Provide a path, server or sign-in.
Navigator Tick the tables or sheets you need.
Load or Transform Straight to the model, or clean first.

Most beginners should choose Transform and look at the data first.

1 / 2

On the Home tab, Get data shows the most common connectors; More... opens a searchable list grouped by category (File, Database, Power Platform, Azure, Online Services, Other). After you pick one, a dialog asks for what the connector needs: a file path, a server and database name, a URL, or a sign-in.

Next comes the Navigator, a window that lists what the source contains. For an Excel file it shows sheets and tables, for a database it shows schemas and tables and views, for a folder it shows files. You tick what you want and see a preview on the right. The two buttons at the bottom decide what happens next:

  • Load puts the data into the model immediately.
  • Transform Data opens the Power Query Editor, where you can clean and shape the data first (chapter 3).

Choose Transform Data by default. It costs one extra click and lets you check types and blank rows before they reach your charts.

Files

Files are the most common starting point.

  • Excel workbook (.xlsx, .xlsm): lists sheets, tables and named ranges. Prefer a formatted Excel table over a loose sheet; a table has defined column names and grows automatically when rows are added.
  • Text/CSV: comma, tab, semicolon or custom delimiters. The preview guesses the delimiter and encoding (UTF-8 is the usual one); fix them in the dialog if accented characters look wrong.
  • JSON and XML: nested formats. Power Query turns them into records and lists that you expand into columns.
  • PDF: Power Query tries to detect tables on each page. It works best for clean, machine-generated PDFs.
  • Parquet and other columnar formats are also available for analytical data.

All of these connectors produce a table in Power Query, so everything you learn about cleaning applies equally to any of them.

Folders

When files arrive every month with the same layout (sales-2025-01.csv, sales-2025-02.csv, and so on), loading them one at a time would be tedious. The Folder connector (and SharePoint folder for files stored in SharePoint or OneDrive for work) lists every file in a folder. You then choose Combine & Transform, and Power Query builds a small function that reads each file and appends the results into a single table. Drop a new file into the folder, refresh, and it appears in the report. Chapter 3 covers this in the lesson on merging and appending.

Databases

For data that lives in a database, use the matching connector: SQL Server, Azure SQL, Oracle, PostgreSQL, MySQL, Snowflake, Google BigQuery, Amazon Redshift, Databricks, and others. You supply a server and optionally a database name. You can choose to select tables from the Navigator or to paste a native SQL query in the Advanced options. Each has trade-offs:

  • Picking tables lets Power Query send filters and aggregations back to the database, which is faster (this is query folding, described in a later lesson).
  • A pasted SQL query is sometimes simpler, but later steps may no longer fold, so Power Query would fetch everything and filter it locally.

Some connectors also ask you to choose between Import and DirectQuery, which the next lesson explains.

Web, APIs and online services

The Web connector reads an HTML table from a page, or a JSON/CSV file at a URL. It can also call web APIs when you give it a URL, query parameters and headers, usually written in M with Web.Contents. OData feeds are another standard way to read from many business systems.

There are also dedicated connectors for online services such as SharePoint lists, Dataverse, Dynamics 365, Salesforce, Google Analytics and many third-party tools. They show up under Online Services. Some require signing in with an organisational account, some with an API key. If a source has no connector, you may be able to use an ODBC driver or write an M query yourself against its web API.

The list of connectors grows nearly every month, and a few are labelled Beta or Preview. Check the in-app list for the current names, and avoid building important production reports on a preview connector.

What a connection looks like in M

Every connection you make is recorded as the first step of a Power Query. In the editor it appears under Applied Steps as Source, and its code appears in the formula bar. You do not have to write it, but reading it helps. The blocks below are read-only: they would run inside Power BI, not on this site.

M
// A CSV file
Source = Csv.Document(
    File.Contents("C:\Data\sales.csv"),
    [Delimiter = ",", Encoding = 65001]
)

// An Excel workbook, then one table from it
Source = Excel.Workbook(File.Contents("C:\Data\budget.xlsx"), null, true),
Budget = Source{[Item = "Budget", Kind = "Table"]}[Data]

// A folder of files
Source = Folder.Files("C:\Data\Monthly")

// A SQL Server table
Source = Sql.Database("sql01.example.com", "SalesDW"),
Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data]

// A web API returning JSON
Source = Json.Document(Web.Contents("https://api.example.com/v1/orders"))

The function name tells you the connector (Csv.Document, Excel.Workbook, Sql.Database), and its arguments are the same values you typed into the dialog. Encoding = 65001 is the code for UTF-8.

Reading the same sources with pandas

You can do the equivalent in pandas. The first example reads CSV text and JSON text, the two formats you will meet most often. In Power BI the file would sit on disk; here the text is embedded so the example is self-contained.

Python Stand-in: CSV and JSON sources

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The folder connector corresponds to "read every file, then stack them". The next example does just that with three small monthly exports, adding a source column the way Combine & Transform keeps the file name.

Python Stand-in: combine a folder of files

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If the files do not all share the same columns, pandas (and Power Query) fill the gaps with nulls, so check the combined result for unexpected empty columns.

Choosing a source: practical advice

  • Use the system of record when you can. Reading from the database that owns the data is more reliable than reading someone's exported spreadsheet.
  • Avoid fragile paths. A path such as C:\Users\Sam\Desktop\sales.csv breaks on every other machine. Use a shared location, SharePoint, or a parameter for the path (see Data Source Settings, Privacy Levels and Parameters).
  • Keep the raw shape stable. Rename columns inside Power Query, not in the source file, so a changed export does not silently break the report.
  • Ask for only what you need. Selecting fewer columns and rows makes refresh faster and the model smaller.
  • Mind the size. Large tables may be better served by a database connection and the right storage mode, which the next lesson discusses.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In the Navigator window, what does the Transform Data button do?

    Transform Data opens Power Query; Load skips it and sends the data straight to the model.
  2. 2. Monthly CSV exports with identical columns arrive in one folder. Which connector is the best fit?

    The Folder connector lists the files and Combine & Transform builds a function that reads each one and appends the rows into one table.
  3. 3. What does Source = Sql.Database("sql01.example.com", "SalesDW") represent?

    Every connection becomes a Source step. Sql.Database takes the server and the database name you typed into the connector dialog.
  4. 4. Why is a hard-coded path like C:\Users\Sam\Desktop\sales.csv a problem?

    Local paths do not exist on other machines or in the cloud. Use a shared location or a parameter instead.

Summary

  • Get data picks a connector, asks for a path, server or sign-in, shows a Navigator and then offers Load or Transform Data.
  • Main families: files (Excel, CSV, JSON, XML, PDF), folders, databases, web/OData, and online services.
  • The Folder connector with Combine & Transform appends many same-shaped files into one table.
  • Each connection is the first applied step, Source, written in M with a function such as Csv.Document or Sql.Database.
  • Prefer the system of record, stable shared paths and only the data you need.
  • In this lesson the connectors were read-only; pandas read_csv, read_json and concat were the stand-ins.

Next, learn what happens to the data once it is connected in Import, DirectQuery and Live Connection.