Connecting Power BI Desktop to Common Enterprise Data Sources: SQL Server, SharePoint, and Azure Data Lake Storage

Introduction

Imagine you've just joined the analytics team at a mid-sized manufacturing company. The operations data lives in a SQL Server database managed by the IT department. The sales team tracks their pipeline in SharePoint lists. And the data engineering team recently migrated two years of raw sensor telemetry into Azure Data Lake Storage. Your job is to build a dashboard that pulls all three together into a single executive view. Where do you even start?

This is not a contrived scenario. It's Tuesday morning for a lot of data professionals. Power BI Desktop is the tool you'll reach for, and its ability to connect to dozens of enterprise data sources is one of its most powerful features. But knowing that a connector exists is very different from knowing how to use it correctly — understanding the options, avoiding common authentication pitfalls, and making deliberate choices about how much work Power BI should do versus how much stays in the source system.

By the end of this lesson, you will be able to connect Power BI Desktop to SQL Server, SharePoint Online lists, and Azure Data Lake Storage Gen2. You'll understand what's happening under the hood at each step, make informed decisions about data loading versus query folding, and troubleshoot the most common connection problems that trip up new users.

What you'll learn:

How to locate and use the Get Data dialog to connect to enterprise sources
How to connect to SQL Server using both Windows and database authentication
How to import SharePoint Online list data into Power BI
How to navigate and load files from Azure Data Lake Storage Gen2
How to choose between Import and DirectQuery modes and why it matters

Prerequisites

Before working through this lesson, you should have:

Power BI Desktop installed on your Windows machine (free from Microsoft's website or the Microsoft Store)
Access credentials for at least one of the three data sources covered — ideally all three, but you can follow along conceptually for the others
A basic understanding of what a table and a column are — no SQL or coding experience required
An internet connection for the SharePoint and Azure connections

Understanding How Power BI Connects to Data

Before touching any connector, it helps to understand what Power BI Desktop is actually doing when you "connect" to a data source.

Power BI Desktop uses a component called Power Query (also known as the M engine) to retrieve and shape data. Think of Power Query as a very capable assistant who goes to your data source, fetches what you ask for, cleans it up according to your instructions, and hands it back to Power BI to store or query. This process is defined by a series of steps recorded in a query, and those steps can either run inside Power BI (against a local copy of your data) or be translated and sent to the source system to execute there.

This brings us to two critically important concepts you'll encounter repeatedly: Import mode and DirectQuery mode.

In Import mode, Power BI copies your data into its own internal storage engine. Your report is fast because it's querying local data, but the data is only as fresh as the last time you refreshed. This is appropriate for most analytical workloads.

In DirectQuery mode, Power BI sends live queries to your source system every time someone interacts with a report visual. Your data is always current, but performance depends on your source system's speed, and not every source supports it.

For most beginners, Import mode is the right choice. We'll call out where DirectQuery is relevant as we go.

Connecting to SQL Server

SQL Server is the most common enterprise database you'll encounter in Microsoft-heavy organizations, so it's a logical place to start.

Opening the SQL Server Connector

In Power BI Desktop, navigate to the Home ribbon tab. You'll see a button labeled Get Data — it either shows the text directly or has a small dropdown arrow. Click the dropdown arrow and you'll see a short list of common sources. Click SQL Server from that list. If you don't see it, click More... to open the full Get Data dialog, then type "SQL" in the search box and select SQL Server Database.

Filling in the Connection Details

A dialog box will appear with two fields:

Server — This is the hostname or IP address of your SQL Server instance. In an enterprise setting, this might look like PROD-SQL-01 (a server name) or prod-sql-01.contoso.internal (a fully qualified domain name). If your SQL Server instance uses a named instance (a configuration where multiple SQL Server installs share one machine), you'll write it as ServerName\InstanceName, for example PROD-SQL-01\REPORTING.

Database (optional) — You can leave this blank and browse all databases on the server, or you can type a specific database name to go directly there. In our manufacturing scenario, the database might be called OperationsDB.

Below those fields, you'll see an Advanced options section you can expand. Two options worth knowing about:

SQL statement — If you already know exactly what data you want, you can write a SQL SELECT query here. For example: SELECT OrderID, ProductCode, Quantity, ShipDate FROM dbo.ProductionOrders WHERE ShipDate >= '2023-01-01'. This pre-filters data at the source, which is more efficient than importing everything and filtering in Power BI.
Command timeout in minutes — Useful if your queries are slow and timing out.

Click OK when ready.

Authenticating

SQL Server supports several authentication methods, and choosing the wrong one is one of the most common beginner mistakes.

A new dialog will appear with a left-side navigation panel showing authentication options. Here's what each means:

Windows — Uses your current Windows login credentials. This is called Windows Authentication or Integrated Security. It works when your PC, your user account, and the SQL Server are all on the same Active Directory domain. In corporate environments this is common and convenient — you don't need to remember a separate password.

Database — Uses a SQL Server username and password (separate from your Windows account). The IT team would provide you these credentials. Use this when you're connecting from outside the domain, or when the SQL Server is set up to use its own user accounts.

Microsoft Account — For Azure SQL Database (SQL Server hosted in Azure), not traditional on-premises SQL Server.

For on-premises corporate SQL Server, try Windows first. If you get an authentication error, ask your DBA (database administrator) whether the server requires SQL credentials.

Once authenticated, Power BI will show you a Navigator panel — a tree view of all the databases, tables, and views on the server. Expand OperationsDB, and you'll see a list of tables. Click a table name to preview its data on the right side. When you're ready to load it, check the box next to the table name. You can select multiple tables here.

At the bottom of the Navigator, you have two buttons: Load and Transform Data.

Load imports the data immediately with no changes.
Transform Data opens the Power Query Editor, where you can filter rows, rename columns, change data types, and more before loading. For anything beyond a simple table import, always click Transform Data first.

Tip: Even if you think you don't need to transform anything, it's worth clicking Transform Data once to verify that Power BI has correctly detected your column data types. A date column mistakenly imported as text will cause problems downstream.

Connecting to SharePoint Online Lists

SharePoint is ubiquitous in Microsoft 365 environments. Teams use SharePoint lists like lightweight databases — tracking project statuses, vendor contracts, customer contacts, and more. Power BI can connect directly to these lists.

Understanding SharePoint Lists

A SharePoint list is roughly equivalent to a database table — it has columns (fields) and rows (items). Unlike an Excel file sitting in a document library, a SharePoint list has structured, queryable data with data types, and it updates in real time as team members add or change records. This makes it a genuinely useful enterprise source, not just a workaround.

Finding Your SharePoint Site URL

Before opening Power BI, you need the URL of your SharePoint site — specifically the site URL, not the URL of the list itself. If your sales team's SharePoint site is at https://contoso.sharepoint.com/sites/SalesTeam, that's the URL you need. The list you want (say, "Q3 Pipeline") lives within that site.

A common mistake is copying the full URL from the browser while viewing a list, which includes extra path segments. Trim it back to just the site root: https://contoso.sharepoint.com/sites/SalesTeam.

Opening the SharePoint List Connector

In Power BI Desktop, go to Home → Get Data → More.... In the search box, type "SharePoint" and you'll see two options:

SharePoint Online List — for Microsoft 365 SharePoint (cloud-hosted). This is almost certainly what you want.
SharePoint List — for older, on-premises SharePoint Server installations.

Select SharePoint Online List and click Connect.

Entering the Site URL and Authenticating

Paste your SharePoint site URL into the text field. You'll also see a radio button asking which implementation to use: 1.0 or 2.0. Use 2.0 unless your list is very large or you're experiencing performance problems — 2.0 retrieves less metadata overhead and is generally faster.

Click OK. Power BI will prompt you to sign in with a Microsoft Account (your work Microsoft 365 account). Click Sign in, complete the login flow in the browser window that pops up, then click Connect.

Selecting Lists in the Navigator

The Navigator will show every list and library on that SharePoint site. This can be a long list — SharePoint generates a lot of internal lists that you don't care about (things like "Site Pages" and "Style Library"). Scroll through or use the search box at the top to find your list by name. In our scenario, look for "Q3 Pipeline."

Click the list name to preview the data. SharePoint lists often include many system columns alongside your actual data — columns like _UIVersionString, ContentTypeId, and AuthorId. You'll want to clean these out in the Power Query Editor.

Click Transform Data rather than Load. In the Power Query Editor, select the columns you want to keep. You can right-click a column header and choose Remove Other Columns to keep only what you've selected, which is faster than removing columns one at a time.

Warning: SharePoint lists have a default threshold of 5,000 items for indexed queries. If your list has more than 5,000 rows, you may encounter retrieval issues. Work with your SharePoint administrator to ensure list view thresholds are configured correctly, or use a different export mechanism for very large lists.

Connecting to Azure Data Lake Storage Gen2

Azure Data Lake Storage Gen2 (often abbreviated ADLS Gen2) is Microsoft's enterprise-grade cloud storage solution for big data analytics. Think of it as a highly scalable file system in the cloud — it stores files (CSV, Parquet, JSON, Delta, and more) organized in a hierarchical folder structure. Data engineering teams use it as a landing zone for raw data and as a staging area for processed datasets.

Understanding the Structure

ADLS Gen2 has a few key concepts:

Storage Account — the top-level container, like the name of the building. Example: contosoanalytics.
Container (also called a filesystem) — a top-level folder within the storage account. Example: raw-data or curated.
Directory/Folder — subfolders within containers.
Files — the actual data files inside folders.

Your data engineers will give you the storage account name and tell you which container and folder path holds the data you need.

Opening the ADLS Gen2 Connector

In Power BI Desktop, go to Home → Get Data → More.... Search for "Azure Data Lake" and select Azure Data Lake Storage Gen2. Click Connect.

Entering the Account URL

The connection dialog asks for a URL. The format is:

https://[storage-account-name].dfs.core.windows.net/

For our scenario, if the storage account is named contosoanalytics, the URL would be:

https://contosoanalytics.dfs.core.windows.net/

Note the .dfs. in the URL — this is specific to ADLS Gen2 (Data Lake Storage uses the DFS, or Distributed File System, endpoint). If you accidentally use .blob. instead, you'll connect to the generic Azure Blob Storage interface and lose some navigation capabilities.

Leave the file path blank for now — you'll navigate to specific files in the Navigator. Click OK.

Authentication Options

ADLS Gen2 offers several authentication methods:

Account Key — A long alphanumeric key that grants full access to the storage account. Your Azure administrator can provide this from the Azure portal. It's simple but broad — anyone with the key has full access.

Organizational Account — Sign in with your Azure Active Directory (now called Microsoft Entra ID) work account. This is the preferred method in most enterprise environments because access is controlled by role-based permissions rather than a shared key.

Shared Access Signature (SAS) — A URL-based token granting time-limited, scoped access. Useful for external parties or temporary access scenarios.

For enterprise use, select Organizational Account, click Sign in, complete the authentication flow, and then click Connect.

Navigating and Loading Files

The Navigator will show you the containers in your storage account. Expand the container your data engineer pointed you to (say, curated), and you'll see a folder structure. Navigate to the folder containing your files.

When you click a folder, Power BI may show you a combined binary view — a table that represents all files in the folder as a single dataset. This is useful when you have many similarly structured files (like monthly CSV exports) that you want to combine into one table. Power BI will automatically parse and stack them.

If you want a single specific file, navigate until you find it and select it. Power BI will show a preview. Parquet files (a columnar binary format common in data lakes) will parse cleanly. CSV files may require you to confirm the delimiter and encoding in the Transform Data step.

Tip: Parquet is almost always preferable to CSV in a data lake context. It stores data in columns rather than rows, which means Power BI can read only the columns it needs rather than scanning entire rows. If your data engineers offer a choice, ask for Parquet.

Making the Right Mode Choice: Import vs. DirectQuery

Now that you know how to connect to all three sources, it's worth pausing to make an informed decision about Import vs. DirectQuery for each.

Source	Import	DirectQuery
SQL Server	Excellent for most cases	Good option if data changes frequently and freshness is critical
SharePoint List	Usually the right choice	Not supported for SharePoint
ADLS Gen2	The only option	Not supported

For SQL Server, if your operations data updates every hour and executives need near-real-time figures, DirectQuery is worth considering — but be aware that every filter click and slicer change will trigger a live query to your database. Coordinate with your DBA to ensure the database can handle that load.

For SharePoint and ADLS Gen2, Import is your only option, so schedule refreshes in the Power BI Service (the cloud publishing platform) to keep data current. Daily or hourly scheduled refreshes cover most business reporting needs.

Hands-On Exercise

Work through the following steps to practice what you've learned. You can complete whichever sections match the systems you have access to.

SQL Server Exercise:

Open Power BI Desktop and click Home → Get Data → SQL Server.
Enter your server name and leave the database field blank.
In the Navigator, explore the available databases. Find a table with at least 5 columns and 100 rows.
Click Transform Data and remove any columns that contain only NULL values or that you wouldn't use in a report.
Click Close & Apply to load the data, then create a simple table visual to verify the data loaded correctly.

SharePoint Exercise:

Navigate to a SharePoint site you have access to in your browser. Copy the site URL (trim it to the site root, not a specific list).
In Power BI Desktop, go to Get Data → More → SharePoint Online List.
Connect to your site, find a list with at least 20 items, and open it in Transform Data.
Remove all system columns (columns starting with underscore or named things like "ContentType," "ID0," "Version") until only meaningful business columns remain.
Load and create a count of items by one category column.

ADLS Gen2 Exercise (if you have access):

Get the storage account name from your Azure administrator or your own Azure subscription.
Connect using the https://[accountname].dfs.core.windows.net/ URL.
Navigate to a CSV or Parquet file, preview it, open in Transform Data, and verify column data types are correctly detected.
Load and create a simple summary visual.

Common Mistakes & Troubleshooting

"I can't see any databases in the Navigator after connecting to SQL Server." This usually means you authenticated successfully but your account doesn't have read permissions on any databases. Contact your DBA and ask them to grant your account at least db_datareader permission on the relevant databases.

"Power BI keeps asking me to sign in to SharePoint every time I open the file." This happens when credentials aren't cached properly. Go to File → Options and Settings → Data Source Settings, find the SharePoint source, and click Edit Permissions. Sign in fresh and check the "Remember my credentials" equivalent option.

"My SharePoint list only shows the first 5,000 rows." You've hit the SharePoint list view threshold. This is a SharePoint-side limitation, not a Power BI bug. Work with your SharePoint admin to index the relevant columns, or consider exporting the list to a different format for larger datasets.

"I'm getting a 403 Forbidden error when connecting to ADLS Gen2." Your account doesn't have the right Azure RBAC (Role-Based Access Control) role on the storage account or container. You need at minimum the Storage Blob Data Reader role. Have your Azure administrator grant this role to your account on the specific container.

"The data in my report is stale." In Power BI Desktop, data doesn't auto-refresh — you need to click the Refresh button in the Home ribbon to pull fresh data from the source. If you've published to the Power BI Service, configure a scheduled refresh under the dataset settings.

"Power Query is running slowly when connecting to SQL Server." If you're importing a very large table, try adding a filter in Power Query (Filter Rows step) on a column like a date field to limit rows. Better yet, use the Advanced SQL Statement option in the connector dialog to write a filtered SELECT query — this pushes the filtering to SQL Server itself, which is much faster than pulling all rows into Power BI and then discarding them.

Summary & Next Steps

You've covered a lot of ground. Here's what you now know how to do:

Use the Get Data dialog to find and connect to enterprise data sources
Connect to SQL Server with both Windows and database authentication, and make informed choices about using a direct SQL query versus full table import
Connect to SharePoint Online Lists, navigate the Navigator, and clean up system columns in Power Query
Connect to Azure Data Lake Storage Gen2 using the correct DFS endpoint URL and organizational authentication, and understand when to choose Parquet over CSV
Understand the difference between Import and DirectQuery mode and when each applies

These three connectors — SQL Server, SharePoint, and ADLS Gen2 — cover the majority of enterprise data scenarios you'll encounter in Microsoft-centric organizations. The skills you've built here transfer directly: every connector in Power BI follows the same pattern of Get Data → authenticate → Navigator → Transform or Load.

Where to go next:

Power Query Transformations — Now that you can get data in, learn how to shape it: splitting columns, merging tables, pivoting data, and handling errors.
Data Modeling in Power BI — Learn how to define relationships between tables from different sources (like joining your SQL operations data to your SharePoint sales pipeline) and build a coherent data model.
Publishing and Scheduling Refresh — Learn how to publish your report to the Power BI Service and set up automated data refreshes so your dashboard stays current without manual intervention.
Gateway Configuration — If your SQL Server is on-premises (not in Azure), you'll need to install and configure an On-Premises Data Gateway to allow the Power BI Service to reach it for scheduled refresh. This is a critical operational topic for enterprise deployments.

Connecting Power BI Desktop to SQL Server, SharePoint, and Azure Data Lake Storage