Power BI + SharePoint
Connect Power BI and SharePoint to Turn Shared Data into Live Insights
Automate data flow between SharePoint lists, libraries, and Power BI dashboards so your teams always work from a single source of truth.
Why integrate Power BI and SharePoint?
Power BI and SharePoint are two of Microsoft's most widely used productivity tools, yet most organizations still move data between them by hand — exporting spreadsheets, uploading files, refreshing reports manually. Integrating the two builds a continuous, automated pipeline that turns raw SharePoint data into visual analytics without anyone lifting a finger. Whether you're tracking project milestones in a SharePoint list or managing inventory in a document library, tray.ai makes it easy to surface those insights directly inside Power BI dashboards.
Automate & integrate Power BI & SharePoint
Use case
Automated SharePoint List Reporting in Power BI
Many teams track projects, tasks, and resources in SharePoint lists, but visualizing trends and KPIs means manually exporting data into spreadsheets. By connecting SharePoint lists directly to Power BI datasets, tray.ai makes sure every new or updated list item shows up in your dashboards automatically. Stakeholders get real-time visibility into project health, task completion rates, and resource allocation without waiting for a weekly report.
Use case
Scheduled Power BI Dataset Refresh from SharePoint Files
Teams frequently store Excel workbooks and CSV files in SharePoint document libraries as their primary data sources for Power BI reports. tray.ai can watch SharePoint libraries for new or modified files and automatically trigger a Power BI dataset refresh, so reports never display stale data. This removes the dependency on manual refreshes or complex gateway configurations.
Use case
Embed Power BI Reports Back into SharePoint Pages
Once insights are generated in Power BI, teams often need to share them with stakeholders who live primarily in SharePoint. tray.ai can automate publishing updated report links or metadata back to SharePoint pages, news posts, or document libraries so reports are always discoverable in context. Data flows from SharePoint into Power BI, and insights flow back to where work happens.
Use case
Power BI Alerts Triggering SharePoint List Updates
Power BI data alerts can notify teams when a metric crosses a threshold — when sales drop below target or inventory falls critically low, for example. By connecting Power BI alerts to tray.ai, those triggers can automatically create or update items in a SharePoint list, starting a structured response workflow directly in SharePoint. No more gap between spotting an alert and actually doing something about it.
Use case
SharePoint Permissions and Governance Reporting in Power BI
IT and compliance teams need to audit SharePoint site permissions, user access, and content changes regularly — but this data is hard to visualize natively. tray.ai can extract SharePoint permission and activity data on a schedule and push it into a Power BI dataset, enabling interactive governance dashboards. Administrators get a clear, always-current picture of who has access to what across their SharePoint environment.
Use case
Cross-Site SharePoint Data Aggregation for Consolidated Power BI Reports
Large enterprises often run dozens of SharePoint sites across departments, each with independent lists and libraries holding related data. tray.ai can aggregate data across multiple SharePoint sites and consolidate it into a unified Power BI dataset, so executive-level reports can span the entire organization. No more piecing together disconnected site-level reports.
Use case
New Power BI Workspace Provisioning Logged to SharePoint
When new Power BI workspaces are created for teams or projects, IT and governance teams need to track the activity for licensing, access control, and auditing. tray.ai can automatically detect new workspace creation events in Power BI and write the relevant metadata — workspace name, owner, creation date, assigned users — to a SharePoint governance list. You get an always-current inventory of Power BI workspaces with no manual logging.
Get started with Power BI & SharePoint integration today
Power BI & SharePoint Challenges
What challenges are there when working with Power BI & SharePoint and how will using Tray.ai help?
Challenge
Keeping Power BI Datasets Fresh as SharePoint Data Changes Continuously
SharePoint lists and files are updated constantly throughout the day by multiple team members, but Power BI dataset refreshes are typically scheduled at fixed intervals or require manual triggering. This lag means dashboards can show outdated information during critical decision-making windows, and frequent refresh schedules can hit Power BI's API rate limits.
How Tray.ai Can Help:
tray.ai's event-driven architecture triggers a Power BI dataset refresh the moment a relevant SharePoint item changes, rather than waiting for a fixed schedule. Tray also handles retry logic and rate-limit backoff automatically, so refresh requests are queued and executed reliably without overwhelming the Power BI API.
Challenge
Handling Schema Mismatches Between SharePoint Columns and Power BI Fields
SharePoint list columns often use data types, naming conventions, and multi-value formats that don't map cleanly to Power BI dataset schemas. Choice columns, person fields, lookup columns, and date formats frequently need transformation before Power BI can ingest them, and maintaining that transformation logic manually is error-prone as schemas change.
How Tray.ai Can Help:
tray.ai provides a visual data mapper and built-in transformation functions so teams can define and maintain field mappings between SharePoint and Power BI without writing custom code. When SharePoint columns change, the mapping can be updated in the tray.ai workflow editor without redeploying scripts or modifying gateway configurations.
Challenge
Authenticating Securely Across SharePoint and Power BI Without Exposing Credentials
Both SharePoint and Power BI use Microsoft identity and OAuth 2.0, but managing service principal credentials, tenant IDs, and permission scopes across both services — especially in multi-tenant or multi-workspace enterprise environments — introduces real security and maintenance complexity. Hard-coded credentials in scripts or gateway configurations create audit and compliance risks.
How Tray.ai Can Help:
tray.ai stores all authentication credentials in an encrypted, centralized credential vault and uses OAuth 2.0 flows natively for both SharePoint and Power BI connectors. Teams can manage service principal permissions in one place, rotate credentials without touching individual workflows, and control who within tray.ai can use each authenticated connection.
Challenge
Aggregating Data Across Hundreds of SharePoint Sites at Scale
Enterprise organizations may have hundreds of SharePoint sites, each with independently managed lists containing related but structurally varied data. Aggregating this for a consolidated Power BI report typically requires custom scripts, scheduled jobs, or complex Power Query M code — all brittle and hard to maintain as the number of sites grows.
How Tray.ai Can Help:
tray.ai supports dynamic looping and conditional branching, so a single workflow can iterate across a dynamically maintained list of SharePoint site URLs, query each one, normalize the output, and consolidate results before pushing to Power BI. Adding a new SharePoint site to the aggregation scope means updating a configuration list — no code changes needed.
Challenge
Knowing When Something Breaks Before a Stakeholder Notices a Stale Dashboard
When SharePoint-to-Power BI sync workflows fail — due to API downtime, permission changes, or data validation errors — teams often have no idea until someone notices a stale dashboard. Silent failures in manual scripts or basic automation tools can go undetected for hours or days, and that erodes trust in the data fast.
How Tray.ai Can Help:
tray.ai provides detailed execution logs, step-level error visibility, and configurable alerting for every workflow run. When a SharePoint or Power BI API call fails, tray.ai can automatically retry the operation, route the failed record to an error-handling branch, and notify the responsible team via email or a messaging tool so nothing slips through unnoticed.
Start using our pre-built Power BI & SharePoint templates today
Start from scratch or use one of our pre-built Power BI & SharePoint templates to quickly solve your most common use cases.
Power BI & SharePoint Templates
Find pre-built Power BI & SharePoint solutions for common use cases
Template
Sync SharePoint List Items to Power BI Dataset on Change
This template monitors a specified SharePoint list for new or updated items and automatically pushes the data into a Power BI streaming dataset or triggers a dataset refresh, keeping dashboards current without manual intervention.
Steps:
- Trigger when a new item is created or an existing item is modified in a specified SharePoint list
- Map SharePoint list fields to the corresponding Power BI dataset schema
- Push the transformed data row to the Power BI streaming dataset or invoke a dataset refresh via the Power BI API
Connectors Used: SharePoint, Power BI
Template
Trigger Power BI Dataset Refresh When SharePoint File Is Updated
This template watches a SharePoint document library for changes to Excel or CSV source files and automatically calls the Power BI API to refresh the dependent dataset, so reports always reflect the latest file version.
Steps:
- Trigger when a file is created or modified in a specified SharePoint document library folder
- Filter to target only files matching the relevant name pattern or extension (e.g., .xlsx, .csv)
- Call the Power BI REST API to initiate a refresh of the dataset linked to that file
Connectors Used: SharePoint, Power BI
Template
Write Power BI Alert Events to SharePoint Action Log
This template captures Power BI data alert notifications and automatically creates corresponding action items in a SharePoint list, so teams can track metric threshold breaches and assign follow-up owners directly within SharePoint.
Steps:
- Receive a Power BI data alert webhook or poll for alert notifications via the Power BI API
- Extract alert metadata including metric name, threshold value, actual value, and timestamp
- Create a new item in the designated SharePoint action log list with the alert details and a default status of 'Open'
Connectors Used: Power BI, SharePoint
Template
Aggregate Multi-Site SharePoint Data into a Consolidated Power BI Dataset
This template queries multiple SharePoint sites on a recurring schedule, consolidates data from their respective lists, normalizes the schema, and pushes the unified dataset into Power BI for organization-wide reporting.
Steps:
- On a defined schedule, iterate through a configured list of SharePoint site URLs and target list names
- Retrieve all items from each list, normalize field names and data types to a common schema, and merge the records
- Upsert the consolidated dataset into Power BI, then trigger a report refresh to surface the latest aggregated data
Connectors Used: SharePoint, Power BI
Template
Log New Power BI Workspace Creations to SharePoint Governance List
This template detects when a new Power BI workspace is provisioned and automatically writes key metadata to a SharePoint governance list, maintaining a live inventory for IT auditing and license management.
Steps:
- Poll the Power BI Admin API on a schedule to detect newly created workspaces since the last run
- Extract workspace metadata including name, ID, owner, capacity, and creation timestamp
- Create a new item in the SharePoint governance list with all extracted metadata and a default review status
Connectors Used: Power BI, SharePoint
Template
Publish Weekly Power BI Report Summaries to SharePoint News
This template runs on a weekly schedule, retrieves key metrics from a Power BI report or dataset, and posts a formatted summary as a SharePoint news item so stakeholders stay informed without opening Power BI directly.
Steps:
- On a weekly schedule, query the Power BI REST API to retrieve specified measures or tile values from a target report
- Format the extracted metrics into a structured HTML or plain-text summary with variance indicators
- Post the formatted summary as a new SharePoint news page item on the designated site, with a deep link back to the full Power BI report
Connectors Used: Power BI, SharePoint