With the typical heads-up, Microsoft announced that a somewhat underutilized feature in SharePoint Online is being phased out. The ability to create a Power BI report directly from a SharePoint list or library using the visualize this list feature will soon be a thing of the past. From this blog post, they provide an HTTP GET call to dump all of the reports, but not much else. What I’m providing in my script is more insight into what sites and lists have the reports bound to them. What my script is lacking is the detail needed to know who created the report or owns it. If you know how to capture this, please let me know, and I’ll update the script.
For this script to run, you will need:
PnP PowerShell
An Azure App Registration
Ability to access your tenant using az login
– It’s recommended that you use an account with Fabric administrator rights, tenant admin rights, or another level of access that can see all-thing-PowerBI.
If you are unable to utilize an App Reg, I provided another script below this one that will output the same basic detail, minus the target list display name.
In your PowerShell terminal, log in using AZ Login, then run the script.
#region Configuration UPDATE THE CONFIG VALUES
$config = @{
ApiEndpoint = "https://api.powerbi.com/v1.0/myorg/admin/groups?`$filter=endswith(name,'SPList')&`$expand=reports,datasets,users&`$top=5000"
OutputFile = "PowerBI_SPList_Workspaces_WithDatasources_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
ClientId = "UPDATE THIS"
Thumbprint = "UPDATE THIS"
Tenant = "YourTenant" #contoso
}
$listDisplayNameCache = @{}
#endregion
#region Helper Functions
function Get-ListGuidFromEmail {
param($emailAddress)
if ($emailAddress -match "SPList-([a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12})") {
return $matches[1]
}
return $null
}
function Get-SharePointListDisplayName {
param([string]$siteUrl, [string]$listGuid)
try {
Connect-PnPOnline -Url $siteUrl -ClientId $config.ClientId -Thumbprint $config.Thumbprint -Tenant "$($config.Tenant).onmicrosoft.com" -ErrorAction Stop
$list = Get-PnPList -Identity $listGuid -ErrorAction Stop
return $list.Title
}
catch {
Write-Host " ⚠️ Could not retrieve list name for GUID $listGuid : $($_.Exception.Message)" -ForegroundColor Yellow
return $null
}
}
function Get-ListInfoFromWorkspace {
param($workspace, $siteUrl)
$listGuid = $null
$listDisplayName = $null
foreach ($user in $workspace.users) {
$extractedGuid = Get-ListGuidFromEmail -emailAddress $user.emailAddress
if ($extractedGuid) {
$listGuid = $extractedGuid
Write-Host " Found List GUID: $listGuid" -ForegroundColor Cyan
$cacheKey = "$siteUrl|$listGuid"
if ($listDisplayNameCache.ContainsKey($cacheKey)) {
$listDisplayName = $listDisplayNameCache[$cacheKey]
Write-Host " Using cached: $listDisplayName" -ForegroundColor Gray
}
else {
Write-Host " Querying SharePoint..." -ForegroundColor Yellow
$listDisplayName = Get-SharePointListDisplayName -siteUrl $siteUrl -listGuid $listGuid
if ($listDisplayName) {
Write-Host " ✅ Display Name: $listDisplayName" -ForegroundColor Green
$listDisplayNameCache[$cacheKey] = $listDisplayName
}
}
break
}
}
return @{
Guid = $listGuid
DisplayName = $listDisplayName
}
}
function New-WorkspaceResult {
param(
$workspace,
$report = $null,
$datasetId = $null,
$datasource = $null,
$listInfo = $null,
$errorStatus = $null
)
$usersList = ($workspace.users | ForEach-Object { "$($_.emailAddress) [$($_.groupUserAccessRight)]" }) -join "; "
return [PSCustomObject]@{
WorkspaceName = $workspace.name
WorkspaceId = $workspace.id
WorkspaceType = $workspace.type
WorkspaceState = $workspace.state
ReportName = $report.name ?? "No Reports"
ReportId = $report.id
ReportWebUrl = $report.webUrl
DatasetId = $datasetId
DatasourceType = $datasource.datasourceType ?? $errorStatus
DatasourceId = $datasource.datasourceId
SharePointSiteUrl = $datasource.connectionDetails.url
SharePointListGuid = $listInfo.Guid
SharePointListDisplayName = $listInfo.DisplayName
ConnectionString = $datasource.connectionDetails.url
GatewayId = $datasource.gatewayId
UserCount = $workspace.users.Count
Users = $usersList
QueryDate = Get-Date
}
}
#endregion
#region Authentication
Write-Host "=== POWER BI WORKSPACE & DATASOURCE QUERY ===" -ForegroundColor Yellow
Write-Host "API Endpoint: $($config.ApiEndpoint)" -ForegroundColor Cyan
Write-Host "Output File: $($config.OutputFile)" -ForegroundColor Cyan
try {
Write-Host "`nGetting access token..." -ForegroundColor Yellow
$token = az account get-access-token --resource https://analysis.windows.net/powerbi/api --query accessToken -o tsv
if (!$token) { throw "Failed to get access token" }
Write-Host "✅ Token retrieved" -ForegroundColor Green
}
catch {
Write-Host "❌ Failed to authenticate: $_" -ForegroundColor Red
Write-Host "Please run 'az login' first" -ForegroundColor Yellow
exit 1
}
#endregion
#region Query Power BI Workspaces
try {
Write-Host "`nQuerying Power BI Admin API..." -ForegroundColor Yellow
$headers = @{ "Authorization" = "Bearer $token"; "Content-Type" = "application/json" }
$response = Invoke-RestMethod -Uri $config.ApiEndpoint -Headers $headers -Method Get
$workspaces = $response.value
Write-Host "✅ Found $($workspaces.Count) workspace(s)" -ForegroundColor Green
if ($workspaces.Count -eq 0) { exit 0 }
}
catch {
Write-Host "❌ API call failed: $_" -ForegroundColor Red
exit 1
}
#endregion
#region Process Workspaces
Write-Host "`n=== PROCESSING WORKSPACES & DATASOURCES ===" -ForegroundColor Yellow
$allResults = @()
$counter = 1
foreach ($workspace in $workspaces) {
Write-Host "`n--- Workspace $counter of $($workspaces.Count) ---" -ForegroundColor Cyan
Write-Host "Name: $($workspace.name)" -ForegroundColor White
$datasetIds = $workspace.reports | Where-Object { $_.datasetId } | Select-Object -ExpandProperty datasetId -Unique
if (-not $datasetIds) {
Write-Host "No datasets found" -ForegroundColor Yellow
$allResults += New-WorkspaceResult -workspace $workspace
$counter++
continue
}
Write-Host "Datasets: $($datasetIds.Count)" -ForegroundColor Green
foreach ($datasetId in $datasetIds) {
Write-Host "`n Dataset: $datasetId" -ForegroundColor Yellow
try {
$datasourceUrl = "https://api.powerbi.com/v1.0/myorg/admin/datasets/$datasetId/datasources"
$datasourcesResponse = Invoke-RestMethod -Uri $datasourceUrl -Headers $headers -Method Get
$datasources = $datasourcesResponse.value
if (-not $datasources -or $datasources.Count -eq 0) {
Write-Host " ⚠️ No datasources found" -ForegroundColor Yellow
continue
}
Write-Host " ✅ Found $($datasources.Count) datasource(s)" -ForegroundColor Green
foreach ($datasource in $datasources) {
Write-Host " Type: $($datasource.datasourceType)" -ForegroundColor Gray
$siteUrl = $datasource.connectionDetails.url
$listInfo = Get-ListInfoFromWorkspace -workspace $workspace -siteUrl $siteUrl
$reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
foreach ($report in $reportsUsingDataset) {
$allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -datasource $datasource -listInfo $listInfo
}
}
}
catch {
Write-Host " ⚠️ Error: $($_.Exception.Message)" -ForegroundColor Yellow
$reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
foreach ($report in $reportsUsingDataset) {
$allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -errorStatus "Error retrieving"
}
}
Start-Sleep -Milliseconds 100
}
$counter++
}
#endregion
#region Export and Summary
if ($allResults.Count -gt 0) {
Write-Host "`n=== EXPORTING RESULTS ===" -ForegroundColor Yellow
$allResults | Export-Csv -Path $config.OutputFile -NoTypeInformation
Write-Host "✅ Exported to: $($config.OutputFile)" -ForegroundColor Green
Write-Host " Total records: $($allResults.Count)" -ForegroundColor Cyan
}
Write-Host "`n=== SUMMARY ===" -ForegroundColor Yellow
Write-Host "Total Workspaces: $($workspaces.Count)" -ForegroundColor Cyan
Write-Host "Total Records: $($allResults.Count)" -ForegroundColor Cyan
$uniqueSites = $allResults | Where-Object { $_.SharePointSiteUrl } | Select-Object -ExpandProperty SharePointSiteUrl -Unique
if ($uniqueSites) {
Write-Host "`nUnique SharePoint Sites: $($uniqueSites.Count)" -ForegroundColor Cyan
$uniqueSites | ForEach-Object { Write-Host " - $_" -ForegroundColor Gray }
}
Write-Host "`n✅ Query completed successfully!" -ForegroundColor Green
#endregion
———- Other script ———————-
Here’s the same basic script, but without needing the Azure App Reg. You will need to grab the SharePointListGuid value, navigate to the corresponding site, open the site contents page, and then click on any list or library settings. In the URL, swap the existing value with the one from SharePointListGuid. This will display the name of the list to which the current report is bound.
Example:https://taco.sharepoint.com/sites/Mas-Taco/_layouts/15/listedit.aspx?List=5ae2c019-75a5-495c-a5a9-beaf47b71346
You’ll want to swap the GUID after ?List=
#region Configuration
$config = @{
ApiEndpoint = "https://api.powerbi.com/v1.0/myorg/admin/groups?`$filter=endswith(name,'SPList')&`$expand=reports,datasets,users&`$top=5000"
OutputFile = "PowerBI_SPList_Workspaces_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
}
#endregion
#region Helper Functions
function Get-ListGuidFromEmail {
param($emailAddress)
if ($emailAddress -match "SPList-([a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12})") {
return $matches[1]
}
return $null
}
function New-WorkspaceResult {
param(
$workspace,
$report = $null,
$datasetId = $null,
$datasource = $null,
$listGuid = $null
)
$usersList = ($workspace.users | ForEach-Object { "$($_.emailAddress) [$($_.groupUserAccessRight)]" }) -join "; "
return [PSCustomObject]@{
WorkspaceName = $workspace.name
WorkspaceId = $workspace.id
WorkspaceType = $workspace.type
WorkspaceState = $workspace.state
ReportName = $report.name ?? "No Reports"
ReportId = $report.id
ReportWebUrl = $report.webUrl
DatasetId = $datasetId
DatasourceType = $datasource.datasourceType
DatasourceId = $datasource.datasourceId
SharePointSiteUrl = $datasource.connectionDetails.url
SharePointListGuid = $listGuid
ConnectionString = $datasource.connectionDetails.url
GatewayId = $datasource.gatewayId
UserCount = $workspace.users.Count
Users = $usersList
QueryDate = Get-Date
}
}
#endregion
#region Authentication
Write-Host "=== POWER BI WORKSPACE QUERY (Simple Version) ===" -ForegroundColor Yellow
Write-Host "API Endpoint: $($config.ApiEndpoint)" -ForegroundColor Cyan
Write-Host "Output File: $($config.OutputFile)" -ForegroundColor Cyan
try {
Write-Host "`nGetting access token..." -ForegroundColor Yellow
$token = az account get-access-token --resource https://analysis.windows.net/powerbi/api --query accessToken -o tsv
if (!$token) { throw "Failed to get access token" }
Write-Host "✅ Token retrieved" -ForegroundColor Green
}
catch {
Write-Host "❌ Failed to authenticate: $_" -ForegroundColor Red
Write-Host "Please run 'az login' first" -ForegroundColor Yellow
exit 1
}
#endregion
#region Query Power BI Workspaces
try {
Write-Host "`nQuerying Power BI Admin API..." -ForegroundColor Yellow
$headers = @{ "Authorization" = "Bearer $token"; "Content-Type" = "application/json" }
$response = Invoke-RestMethod -Uri $config.ApiEndpoint -Headers $headers -Method Get
$workspaces = $response.value
Write-Host "✅ Found $($workspaces.Count) workspace(s)" -ForegroundColor Green
if ($workspaces.Count -eq 0) { exit 0 }
}
catch {
Write-Host "❌ API call failed: $_" -ForegroundColor Red
exit 1
}
#endregion
#region Process Workspaces
Write-Host "`n=== PROCESSING WORKSPACES ===" -ForegroundColor Yellow
$allResults = @()
$counter = 1
foreach ($workspace in $workspaces) {
Write-Host "`n--- Workspace $counter of $($workspaces.Count) ---" -ForegroundColor Cyan
Write-Host "Name: $($workspace.name)" -ForegroundColor White
# Extract list GUID from workspace users (no SharePoint connection needed)
$listGuid = $null
foreach ($user in $workspace.users) {
$extractedGuid = Get-ListGuidFromEmail -emailAddress $user.emailAddress
if ($extractedGuid) {
$listGuid = $extractedGuid
Write-Host "List GUID: $listGuid" -ForegroundColor Cyan
break
}
}
$datasetIds = $workspace.reports | Where-Object { $_.datasetId } | Select-Object -ExpandProperty datasetId -Unique
if (-not $datasetIds) {
Write-Host "No datasets found" -ForegroundColor Yellow
$allResults += New-WorkspaceResult -workspace $workspace -listGuid $listGuid
$counter++
continue
}
Write-Host "Datasets: $($datasetIds.Count)" -ForegroundColor Green
foreach ($datasetId in $datasetIds) {
Write-Host " Dataset: $datasetId" -ForegroundColor Yellow
try {
$datasourceUrl = "https://api.powerbi.com/v1.0/myorg/admin/datasets/$datasetId/datasources"
$datasourcesResponse = Invoke-RestMethod -Uri $datasourceUrl -Headers $headers -Method Get
$datasources = $datasourcesResponse.value
if (-not $datasources -or $datasources.Count -eq 0) {
Write-Host " No datasources found" -ForegroundColor Yellow
continue
}
Write-Host " ✅ Found $($datasources.Count) datasource(s)" -ForegroundColor Green
foreach ($datasource in $datasources) {
$siteUrl = $datasource.connectionDetails.url
Write-Host " Site: $siteUrl" -ForegroundColor Gray
$reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
foreach ($report in $reportsUsingDataset) {
$allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -datasource $datasource -listGuid $listGuid
}
}
}
catch {
Write-Host " ⚠️ Error: $($_.Exception.Message)" -ForegroundColor Yellow
$reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
foreach ($report in $reportsUsingDataset) {
$allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -listGuid $listGuid
}
}
Start-Sleep -Milliseconds 100
}
$counter++
}
#endregion
#region Export and Summary
if ($allResults.Count -gt 0) {
Write-Host "`n=== EXPORTING RESULTS ===" -ForegroundColor Yellow
$allResults | Export-Csv -Path $config.OutputFile -NoTypeInformation
Write-Host "✅ Exported to: $($config.OutputFile)" -ForegroundColor Green
Write-Host " Location: $(Get-Location)$($config.OutputFile)" -ForegroundColor Green
Write-Host " Total records: $($allResults.Count)" -ForegroundColor Cyan
}
Write-Host "`n=== SUMMARY ===" -ForegroundColor Yellow
Write-Host "Total Workspaces: $($workspaces.Count)" -ForegroundColor Cyan
Write-Host "Total Records: $($allResults.Count)" -ForegroundColor Cyan
# Show unique SharePoint sites found
$uniqueSites = $allResults | Where-Object { $_.SharePointSiteUrl } | Select-Object -ExpandProperty SharePointSiteUrl -Unique
if ($uniqueSites) {
Write-Host "`nUnique SharePoint Sites: $($uniqueSites.Count)" -ForegroundColor Cyan
$uniqueSites | ForEach-Object { Write-Host " - $_" -ForegroundColor Gray }
}
# Show unique list GUIDs found
$uniqueListGuids = $allResults | Where-Object { $_.SharePointListGuid } | Select-Object -ExpandProperty SharePointListGuid -Unique
if ($uniqueListGuids) {
Write-Host "`nUnique SharePoint List GUIDs: $($uniqueListGuids.Count)" -ForegroundColor Cyan
$uniqueListGuids | ForEach-Object { Write-Host " - $_" -ForegroundColor Gray }
}
Write-Host "`n✅ Query completed successfully!" -ForegroundColor Green
#endregion
