Power BI reports often rely on SharePoint Online as a data source. Using PnP PowerShell, we can efficiently manage:
Connecting Power BI to SharePoint Online lists and libraries
Updating Power BI dataset connections
Automating dataset refresh schedules
Managing permissions for Power BI reports linked to SharePoint
Step 1: Connect to SharePoint Online
Before managing Power BI connections, authenticate with SharePoint Online:
$siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
Connect-PnPOnline -Url $siteUrl -Interactive
Write-Host " Connected to SharePoint Online"
✔ Ensures secure authentication to access SharePoint data sources.
Step 2: Retrieve SharePoint Online Lists for Power BI
Fetch all lists available in the SharePoint Online site:
$lists = Get-PnPList | Select Title, Id, ItemCount
$lists | Format-Table -AutoSize
Write-Host " Retrieved all SharePoint lists for Power BI connection"
✔ Helps Power BI users select the correct SharePoint data source.
Step 3: Get Power BI Datasets Connected to SharePoint Online
To manage Power BI datasets, connect to Power BI Service (requires Power BI Admin rights):
Connect-PowerBIServiceAccount
$datasets = Get-PowerBIDataset | Where-Object { $_.WebUrl -like "*sharepoint.com*" }
Write-Host " Power BI datasets connected to SharePoint:"
$datasets | Select Name, WebUrl | Format-Table -AutoSize
✔ Identifies which Power BI datasets are linked to SharePoint Online.
Step 4: Update SharePoint Online Connection in Power BI Dataset
If a SharePoint site URL changes, update the Power BI dataset connection:
$datasetName = "Sales Report Dataset"
$newSharePointUrl = "https://yourtenant.sharepoint.com/sites/NewDataSite"
$dataset = Get-PowerBIDataset | Where-Object { $_.Name -eq $datasetName }
Set-PowerBIDataset -Id $dataset.Id -ConnectionDetails @{
Url = $newSharePointUrl
}
Write-Host " Updated Power BI dataset connection to: $newSharePointUrl"
✔ Ensures Power BI reports point to the correct SharePoint data source.
Step 5: Automate Power BI Dataset Refresh for SharePoint Data
Schedule an automatic dataset refresh for SharePoint-based reports:
$dataset = Get-PowerBIDataset | Where-Object { $_.Name -eq "Sales Report Dataset" }
Invoke-PowerBIDatasetRefresh -Id $dataset.Id
Write-Host " Power BI dataset refresh triggered for SharePoint data"
✔ Ensures real-time updates in Power BI reports.
Step 6: Manage Power BI Report Permissions for SharePoint Users
To allow SharePoint users to access Power BI reports, assign workspace roles:
$workspaceName = "Sales Reports"
$userEmail = "user@yourtenant.com"
$role = "Viewer" # Options: Admin, Contributor, Member, Viewer
$workspace = Get-PowerBIWorkspace | Where-Object { $_.Name -eq $workspaceName }
Add-PowerBIWorkspaceUser -Id $workspace.Id -UserPrincipalName $userEmail -AccessRight $role
Write-Host " Added $userEmail as $role in Power BI workspace: $workspaceName"
✔ Grants controlled access to Power BI reports linked to SharePoint.
Step 7: Automate Power BI and SharePoint Connection Management
Schedule the PowerShell script to run weekly:
$taskName = "Power BI & SharePoint Connection Sync"
$scriptPath = "C:\Scripts\ManagePowerBIConnections.ps1"
$action = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-File $scriptPath"
$trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Sunday -At 2AM
Register-ScheduledTask -TaskName $taskName -Action $action -Trigger $trigger -User "SYSTEM" -RunLevel Highest
Write-Host " Automated Power BI and SharePoint connection management scheduled."
✔ Keeps Power BI reports synced with SharePoint Online.