Connect Power BI Desktop to SharePoint Folder

Connecting your SharePoint data to Power BI dashboards is simple and efficient. You can turn SharePoint chaos into clear, actionable insights using Microsoft Power BI. Many users report these benefits when they connect SharePoint:

• Quick move from data review to real decisions
• Dashboards on both web and mobile
• Alerts when data updates
• Strong data security
• Easy-to-use interface for all skill levels
• Enhanced teamwork with real-time data

You do not need advanced skills to start. If you face issues like data refresh failures or permission problems, this guide will help you solve them. Follow along to unlock the full potential of your SharePoint data.

Key Takeaways

• Connecting SharePoint data to Power BI is simple and enhances decision-making.
• Use Power BI Desktop to build and visualize your data easily.
• Ensure you have the right permissions to access SharePoint data before starting.
• Identify the type of SharePoint data you want to connect for a smoother integration.
• Select the appropriate connector based on your data type for optimal performance.
• Always preview and filter your data in Power BI to avoid unnecessary imports.
• Schedule regular data refreshes to keep your dashboards updated with the latest information.
• Organize your SharePoint data well to improve reporting quality and dashboard effectiveness.

Prerequisites for Power BI and SharePoint

Before you connect SharePoint data to Power BI dashboards, you need to prepare your tools and information. This preparation helps you avoid common problems and ensures a smooth experience.

5 Surprising Facts about Using Power BI with SharePoint Folders

If you need to connect SharePoint to Power BI, here are five surprising facts that can improve your workflows and avoid common pitfalls.

1. Folder-level refresh can skip changed files: Power BI’s SharePoint Folder connector treats the folder as a single data source; when files are added or updated, incremental refresh or cache settings may not detect individual file changes unless folder metadata (like modified date) is included in the query.
2. Authentication differences matter for the same URL: The same SharePoint site URL can require different authentication methods (OAuth vs. Organizational) depending on how you connect (SharePoint Folder vs. SharePoint Online List), which can cause refresh failures in Power BI Service if not configured consistently.
3. Hidden files and system files appear by default: When you use the SharePoint Folder connector, Power BI returns all files in the folder including hidden/system files (like desktop.ini or ~$ temp files). Filtering by file extension or path is often necessary to avoid import noise.
4. Relative paths enable portability but break automatic refresh if not set right: Using relative paths and the SharePoint site root in queries makes PBIX files portable across environments, but Power BI Service needs correctly configured gateway credentials and the same base URL to refresh; mismatches lead to unknown data source errors.
5. SharePoint metadata can be richer than file content: SharePoint stores metadata (columns, custom properties) that you can pull alongside file contents in Power BI; sometimes that metadata provides more valuable reporting fields than parsing file contents themselves, letting you avoid heavy data transformations.

Install Power BI Desktop

You need Power BI Desktop to connect and work with SharePoint data. Power BI Desktop is a free application from Microsoft. It lets you build, transform, and visualize your data before sharing it with others. Make sure your computer meets the minimum system requirements. The table below shows what you need:

You can download Power BI Desktop from the official Microsoft website. After installation, check that your display settings and browser are compatible. This step prevents display or loading issues later.

Access and Permissions

You must have the right permissions to access SharePoint data from Power BI. SharePoint uses security settings to control who can view or edit data. Power BI respects these settings, so you need to make sure you have access before you start.

If you do not have the right permissions, you may see errors or missing data. Ask your SharePoint administrator to confirm your access level if you are unsure.

Identify SharePoint Data Type

You should know what kind of data you want to connect to Power BI. SharePoint stores data in different ways, such as lists, folders, or document libraries. Each type works best with a specific connector in Power BI. For example, use the SharePoint List connector for structured lists, and the SharePoint Folder connector for files and documents.

Tip: Make a list of the SharePoint sites, lists, or folders you want to use. Write down their URLs and check if you have access. This step saves time and helps you avoid mistakes during setup.

By preparing these prerequisites, you set yourself up for a successful connection between SharePoint and Power BI. You will spend less time troubleshooting and more time building useful dashboards.

Gather Site URLs and Credentials

You need to collect the correct SharePoint site URLs and credentials before connecting your data to Power BI. This step helps you avoid errors and ensures a smooth integration. SharePoint stores data in lists, folders, and document libraries. Each type requires a specific URL format. You must know where your data lives and how to access it.

Start by identifying the SharePoint site that contains your data. Look for the site address in your browser’s address bar when you visit the SharePoint page. The site URL usually looks like this:

https://yourcompany.sharepoint.com/sites/yoursite

If you want to connect to a list, you need the site URL, not the list URL. For folders or document libraries, use the main site address. Write down these URLs in a notebook or a digital document. Keeping a record prevents mistakes during setup.

Tip: Double-check the URLs for typos or extra characters. Incorrect URLs cause connection errors in Power BI.

You also need your SharePoint credentials. These are usually your organizational email and password. Power BI uses these credentials to authenticate your access. If your organization uses multi-factor authentication, prepare your phone or authentication app. You may need to approve the sign-in during the connection process.

Here is a simple checklist to help you gather what you need:

1. Find the SharePoint site URL for your data.
2. Confirm you have access to the site and the specific list or folder.
3. Write down your organizational email and password.
4. Prepare for multi-factor authentication if required.
5. Check that your data is clean and structured for Power BI.

You must ensure your SharePoint lists or document libraries have clean, structured data. Set permissions so Power BI can access the information. If you do not have access, contact your SharePoint administrator. They can grant the necessary permissions.

When you connect Power BI to SharePoint, you will enter the site URL and your credentials. Power BI will prompt you to authenticate. Enter your email and password, then follow any additional steps for multi-factor authentication. This process allows Power BI to pull data from SharePoint securely.

Note: Power BI respects SharePoint security settings. You only see data you have permission to access.

After you gather your URLs and credentials, you are ready to connect SharePoint to Power BI. This preparation saves time and reduces errors. You can focus on building dashboards and transforming your data into insights.

Choose the Right Connector

Selecting the right connector is essential when you want to connect sharepoint data to Power BI. Each connector serves a specific purpose and works best with certain types of SharePoint data. You can make your integration smoother by understanding the differences and knowing when to use each option.

SharePoint Folder Connector

When to Use

You should use the SharePoint Folder Connector when your data is stored as files within SharePoint document libraries or folders. This connector is ideal for situations where you need to import Excel files, CSVs, or other documents from multiple folders. If your data is distributed across several files, this connector helps you gather everything in one place.

Tip: Choose this connector if you want to analyze files rather than structured lists.

Supported Data

The SharePoint Folder Connector supports files such as Excel workbooks, CSV files, and other document types. You can pull metadata from these files, including file names, paths, and modification dates. This connector works well for document libraries that contain many files.

SharePoint List Connector

When to Use

You should use the SharePoint List Connector when you want to retrieve data from a SharePoint list. SharePoint lists are collections of structured data, such as tasks, contacts, or inventory. This connector works for both SharePoint Online and on-premise SharePoint environments.

Note: Use this connector for structured lists shared with your team.

Supported Data

The SharePoint List Connector supports structured list data. You can access columns, rows, and metadata from lists. This connector is best for standard list data that you want to visualize or analyze in Power BI.

• Task lists
• Contact lists
• Inventory lists
• Custom lists

SharePoint Online List Connector

Key Differences

The SharePoint Online List Connector is designed specifically for SharePoint Online. It simplifies access to structured lists and offers improved performance compared to other connectors. The 2.0 version is optimized for speed and works well with standard list data. The 1.0 version provides access to additional metadata, which is useful for complex analysis.

• Connects only to SharePoint Online
• Offers faster performance for standard lists
• Provides access to extra metadata in version 1.0

Best Use Cases

You should use the SharePoint Online List Connector when you need quick access to structured lists in SharePoint Online. This connector is effective for dashboards that require up-to-date information and fast refresh rates. It works well for scenarios where you want to build reports from standard lists and need reliable performance.

Callout: If you want more control and flexibility, consider using the SharePoint API. The API gives you greater control over your data and improves performance, but it requires more technical knowledge.

When you connect sharepoint data to Power BI, you must choose the connector that matches your data type and your reporting needs. This choice ensures you get the best performance and the most accurate results.

Step-by-Step: Connect SharePoint Data

Connecting your SharePoint data to Power BI involves a few clear steps. You can follow this guide to ensure a smooth and successful integration.

Open Power BI and Get Data

Start by launching Power BI Desktop on your computer. You will use the “Get Data” feature to begin the process. Here is a simple step-by-step guide:

1. Open Power BI Desktop.
2. Go to the Home tab and select “Get Data.”
3. In the Get Data window, search for “SharePoint.”
4. Choose the connector that matches your data type, such as “SharePoint Online List” or “SharePoint Folder.”
5. Click “Connect” to proceed.

You can also use the “Web” option if you want to import a specific file from SharePoint. In this case, paste the direct file path from SharePoint and authenticate when prompted. Power BI will then import only the specified file for your report.

If you need to connect to a SharePoint list using OData, select “OData Feed” and enter the OData Feed URL. After connecting, select the required list in the Navigator dialog and load it into Power BI.

Enter SharePoint Site URL

After selecting the right connector, you need to enter the SharePoint site URL. The correct URL format is important for a successful connection.

URL Format for Folders

When you want to connect to a SharePoint folder or document library, use the main site address. The URL should look like this:

“https://yourcompany.sharepoint.com/sites/yoursite“

Make sure you do not include extra paths or file names. Only use the base site URL for folders.

URL Format for Lists

For SharePoint lists, you also need the base site URL. Do not use the full list address. The correct format is:

“https://yourcompany.sharepoint.com/sites/yoursite“

This format allows Power BI to find all available lists on the site. You can then select the specific list you want to use.

Tip: Always double-check your URL for typos. The URL must start with “https://” or “http://”. This ensures Power BI recognizes it as a valid web address.

Authenticate Connection

Once you enter the SharePoint site URL, Power BI will prompt you to authenticate your connection. This step ensures that only authorized users can access the data.

Organizational Account

You should use your organizational account to sign in. Power BI supports authentication methods such as OAuth 2.0 and user credentials. Enter your work email and password when prompted. This process allows Power BI to respect your SharePoint permissions and keep your data secure.

Multi-Factor Authentication

Many organizations require multi-factor authentication for added security. If your company uses this feature, you may need to approve the sign-in using your phone or an authentication app. Follow the on-screen instructions to complete the process. Once authenticated, Power BI will connect sharepoint data to your workspace.

Note: Power BI uses your SharePoint permissions. You will only see the data you have access to.

By following these steps, you can connect sharepoint data to Power BI quickly and securely. This process helps you move from raw data to actionable insights with ease.

Select and Transform Data

After you connect to your SharePoint source, you will see a Navigator window in Power BI. This window lets you preview your data before you load it. You can select the tables, lists, or files you want to work with. This step helps you avoid importing unnecessary information and keeps your reports focused.

Preview and Filter

You should always preview your data before loading it into Power BI. The preview feature lets you check the structure, column names, and sample values. This helps you spot issues early, such as missing headers or unexpected data types.

• Use the preview to verify that you selected the correct list or file.
• Check for extra columns or rows that you do not need.
• Apply filters to limit the data to only what is relevant for your report.

Early filtering improves performance. When you filter out unnecessary rows or columns at this stage, Power BI processes less data, which speeds up loading and refresh times. For example, you can filter by date, status, or any other field that helps narrow your results.

Tip: Apply filters as early as possible in your query. This practice reduces the amount of data Power BI needs to process and keeps your dashboards fast.

Use Power Query

Power Query is a powerful tool inside Power BI that lets you shape and transform your SharePoint data. You can clean, restructure, and combine data from different sources. Power Query uses a step-by-step approach, so you can see each transformation and undo changes if needed.

Here are some best practices for transforming SharePoint data with Power Query:

• Define the scope of your query. Decide which columns, rows, and data types you need.
• Use the “Choose columns” feature to select only the columns you want.
• Remove unnecessary rows, such as headers or footers, using “Remove top rows” or “Remove bottom rows.”
• Promote the first row to headers if your data does not have proper column names.
• Rename columns for clarity and consistency.
• Unpivot columns to turn wide tables into long tables, which are easier to analyze.
• Cleanse data by removing errors or replacing special characters.
• Combine multiple tables into one main table if your data is split across several lists or files.
• Break down complex queries into smaller, manageable steps for easier troubleshooting.
• Use parameters to create dynamic queries. Parameters let you reuse values and make your queries flexible.
• Create custom functions to apply the same transformation to multiple queries. This saves time and keeps your transformations consistent.
• Develop reusable query templates for repetitive tasks.
• Document your transformations. Add descriptions to each step so you and your team can understand the process later.
• Practice good data privacy and security settings, especially when you connect sharepoint data from external sources.

Note: Regularly use the preview feature in Power Query to check your transformations. This helps you catch errors before loading data into your report.

You can also master the basics of the M language for advanced transformations. Power Query supports query folding, which means it pushes transformations back to the data source when possible. This improves performance, especially with large datasets.

Load Data to Power BI

Once you finish transforming your data, you can load it into Power BI. You have several options for loading data, and your choice affects performance and usability.

To load your data:

1. Click “Close & Apply” in Power Query.
2. Power BI imports the transformed data into your workspace.
3. You can now build visuals, create relationships, and design your dashboard.

If you work with large datasets, consider loading only a sample of the data first. This approach lets you test your transformations and visuals without waiting for a full data load. You can refresh the data later to bring in the complete dataset.

Tip: Use consistent naming conventions for your tables and columns. Clear names make it easier to build and maintain your Power BI reports.

By following these steps, you ensure that your SharePoint data is clean, structured, and ready for analysis. You set a strong foundation for building insightful dashboards that drive better decisions.

Troubleshoot Connection Issues

Invalid URL

You may see an error message like “The URL is not valid or you do not have permission to the site” when connecting Power BI to SharePoint. This problem often happens because of mistakes in the URL or issues with special characters.

Common causes of invalid URL errors include:

Tip: Always copy the SharePoint site URL directly from your browser’s address bar. Double-check for extra spaces or characters.

If you still see the error, try resetting your credential properties in Power BI. Sometimes, old or incorrect credentials can block access. Also, ask your SharePoint administrator to confirm your permissions if you are unsure.

Authentication Errors

Authentication errors can stop you from connecting Power BI to SharePoint. These errors often relate to how you sign in or how Power BI handles security.

To fix authentication errors, follow these steps:

1. Use the SharePoint Folder connector if you want to access multiple files.
2. Enter the root URL of the SharePoint site, not the folder path.
3. Make sure your credentials match those you use to access SharePoint in your browser.

Power BI Desktop uses the Active Directory Authentication Library (ADAL). This means it does not allow redirects to non-secured URLs. If you see an error code like ‘non_https_redirect_failed’, check that all URLs use HTTPS. Power BI does not support redirects to HTTP addresses.

Note: Authentication errors can also happen because of proxy servers, tenant settings, or connector misconfigurations. Review your organization’s network and security settings if problems continue.

Missing Data

Sometimes, you connect to SharePoint but do not see all your files or list items in Power BI. Missing data can have several causes.

Check these common reasons:

A user once found that an Excel file saved in SharePoint did not appear in Power BI. This can happen if the connection settings are wrong or if the file is too large. Another user saw only one row instead of a full list, which pointed to a possible URL or API version issue.

Tip: Refresh your queries in Power Query to make sure you import all files. If you still miss data, check your permissions and the SharePoint folder setup.

If you want to work with SharePoint list data in Excel, you can copy the query code from Power BI and use it in Excel’s Power Query. This method lets you transform and load data as needed.

By understanding these common issues, you can solve most connection problems and keep your Power BI dashboards up to date.

Refresh Problems

You may notice that your Power BI dashboard does not show the latest SharePoint data right away. Refresh problems can cause confusion and delay important decisions. You can solve these issues by understanding common causes and applying practical solutions.

Power BI dashboards update data on a schedule or when you refresh manually. Sometimes, the dashboard tiles do not reflect new data immediately. You should wait 10 to 15 minutes after a refresh. If the visualization does not update, try repinning it to your dashboard. This action forces Power BI to display the latest information.

You may see errors like GatewayNotReachable when Power BI cannot connect to SharePoint. This error often means the gateway is outdated or not installed. You should install the latest gateway version and retry setting your credentials. Keeping your gateway updated ensures a stable connection between Power BI and SharePoint.

Token expiration can also cause refresh failures. If you use different accounts for Power BI service and SharePoint Online, the token may expire. You should use the same account for both services. This practice keeps your authentication valid and prevents refresh errors.

Scheduled refreshes may stop working after a failure. You need to fix the underlying issue, such as updating credentials or resolving permission problems. After you fix the issue, re-enable scheduled refresh in Power BI. This step restores automatic updates and keeps your dashboard current.

Cached credentials can block data refresh. You should clear your browser cache and update your credentials at the link provided in Power BI. This action removes old authentication tokens and allows Power BI to access SharePoint data again.

The table below summarizes solutions for common refresh problems:

Tip: You should check your refresh history in Power BI. The refresh history shows errors and helps you find the cause of the problem. Reviewing this log saves time and guides your troubleshooting steps.

You can keep your dashboards reliable by following these solutions. Regular maintenance and careful monitoring help you avoid refresh problems and ensure your SharePoint data stays up to date in Power BI.

Best Practices for SharePoint Data

Keep Data Updated

You need to keep your SharePoint data current for accurate Power BI dashboards. Outdated data can lead to wrong decisions. You can use several methods to make sure your data stays fresh:

1. Upload Excel or XML files to a SharePoint library.
2. Set up Power Automate to trigger a data refresh as soon as you upload a file.
3. Let the Power BI semantic model update with the latest data.
4. Use Push Mode in Power BI to refresh your dashboard in real time.
5. Publish the updated report so your team can view it in SharePoint, Teams, or your website.
6. Notify your team automatically through Teams or email when the update finishes.

You can also keep your .pbix files in SharePoint. In Power BI Service, choose to import your report from SharePoint. This method creates a live connection, so your dashboard always shows the latest data.

Tip: Schedule regular data refreshes in Power BI to avoid manual updates and keep your reports accurate.

Organize for Reporting

You should organize your SharePoint data before connecting it to Power BI. Well-structured data makes reporting easier and improves dashboard quality. Start by storing your information in SharePoint lists or document libraries. Clean and organize your data so Power BI can read it without errors.

You can also create specific lists for different types of data. For example, use one list for “Donor Contributions” and another for “Event Registrations”. This approach keeps your data organized and easy to find.

• Store data in SharePoint lists or libraries.
• Make sure your data is clean and well-organized.
• Create separate lists for each data type.

Note: Structured data helps Power BI create better visuals and faster reports.

Secure Sensitive Data

You must protect sensitive data when you connect SharePoint to Power BI. Security keeps your information safe and builds trust with your team. Follow these steps to secure your data:

1. Give users only the permissions they need.
2. Use strong authentication, like multi-factor authentication (MFA).
3. Label and classify sensitive data with Microsoft Information Protection.
4. Review access regularly and remove unused permissions.
5. Monitor user activity for unusual behavior.
6. Turn on encryption for data in transit and at rest.
7. Limit sharing and exporting of sensitive reports.
8. Train your team on Power BI security best practices.
9. Keep all software up to date with the latest security patches.
10. Plan how to respond if a data breach happens.

Alert: Never share sensitive Power BI reports outside your organization unless you have approval and proper controls.

By following these best practices, you can keep your SharePoint data accurate, organized, and secure. This foundation helps you build reliable Power BI dashboards that support smart decisions.

Build and Share Power BI Dashboards

Create Visuals

You can turn SharePoint data into clear visuals using Power BI. Start by choosing the right visual for your data. Tables help you display raw information in a structured way. Bar and column charts let you compare categories or track changes over time. Line charts show trends and patterns. Pie charts work well for showing proportions, but you should limit them to six or seven categories for easy reading. Donut charts offer a similar view with a central hole.

Tip: Use bar and column charts for comparisons. Choose line charts to highlight trends. Keep pie charts simple for clarity.

You can drag fields onto the canvas in Power BI. Adjust colors, labels, and filters to make your dashboard easy to understand. Arrange visuals so users can find key information quickly.

Publish and Share

After you build your dashboard, you can share it with your team. Power BI makes publishing simple. Open your report in the Power BI service. On the File menu, select Embed report and choose SharePoint Online. Copy the report URL from the dialog box.

Follow these steps to add your dashboard to SharePoint:

1. Open the target page in SharePoint Online and select Edit.
2. Click the plus sign in the New dropdown menu.
3. In the Data analysis section, select the Power BI web part.
4. Select Add report.
5. Paste the report URL into the Power BI report link field. The report loads automatically.
6. Select Publish to make the dashboard visible to your SharePoint users.

Note: You can embed Power BI dashboards in SharePoint pages. This lets your team view insights without leaving their workspace.

Schedule Data Refresh

You need to keep your dashboard up to date. Power BI lets you schedule automatic data refreshes. Under the Semantic Model, find the Scheduled refresh section. Enable the refresh and set your preferred schedule. You can choose daily, weekly, or multiple times per day.

• Monitor the availability and connectivity of your data sources.
• Review and adjust refresh settings as your business needs change.

Alert: Regular refreshes ensure your dashboard always shows the latest SharePoint data. Check your settings often to avoid missing updates.

You can build, share, and maintain Power BI dashboards with ease. These steps help you turn SharePoint data into actionable insights for your team.

You can connect sharepoint data to Power BI dashboards with ease and see immediate benefits. This process uses your existing information structure, avoids data duplication, and keeps reports accurate with live updates. You can start small and expand as your needs grow.

• Seamless integration fits into your workflow
• Teams can comment and share insights directly
• Automated reports keep everyone informed

Explore more Power BI features to unlock even greater value from your SharePoint data.

Connect SharePoint to Power BI Desktop – Checklist

FAQ

How do you find the correct SharePoint site URL for Power BI?

You can copy the site URL from your browser’s address bar when you visit your SharePoint site. Make sure you use the main site address, not a file or list-specific link.

What permissions do you need to connect Power BI to SharePoint?

You need at least “Read” access to the SharePoint site, list, or library. If you cannot see the data in SharePoint, you will not see it in Power BI.

Can you refresh Power BI dashboards automatically with SharePoint data?

Yes. You can set up scheduled refreshes in Power BI Service. This keeps your dashboards updated with the latest SharePoint data without manual steps.

Which SharePoint connector should you use for document libraries?

You should use the SharePoint Folder connector. This connector works best for importing files like Excel or CSV from document libraries.

Can you share Power BI dashboards with people outside your organization?

You can share dashboards with external users if your admin allows guest access. Always follow your company’s data sharing policies before sharing sensitive information.

Does Power BI support multi-factor authentication for SharePoint connections?

Yes. Power BI supports multi-factor authentication. You may need to approve sign-ins using your phone or authentication app during the connection process.

🚀 Want to be part of m365.fm?

Then stop just listening… and start showing up.

👉 Connect with me on LinkedIn and let’s make something happen:

• 🎙️ Be a podcast guest and share your story
• 🎧 Host your own episode (yes, seriously)
• 💡 Pitch topics the community actually wants to hear
• 🌍 Build your personal brand in the Microsoft 365 space

This isn’t just a podcast — it’s a platform for people who take action.

🔥 Most people wait. The best ones don’t.

👉 Connect with me on LinkedIn and send me a message:
“I want in”

Let’s build something awesome 👊