Since this issue can only occur with reports, I have solved it by looking at the pipeline stage artifacts endpoint of the Power BI REST API (link here).
The Powershell code to delete orphaned reports is this:
$subscriptionId = "<YOUR DEFAULT SUBSCRIPTION>"
$testWorkspaceStage = "<YOUR STAGE NUMBER HERE (0 is the first stage (usually dev) etc)>"
$pipelineId = "<YOUR DEPLOYMENT PIPELINE ID>"
$resourceUrl = "https://api.fabric.microsoft.com"
$fabricHeaders = @{}
function SetFabricHeaders() {
# Set default subscription
Update-AzConfig -DefaultSubscriptionForLogin "$subscriptionId"
# Login to Azure
Connect-AzAccount | Out-Null
# Get authentication
$fabricToken = (Get-AzAccessToken -ResourceUrl $resourceUrl).Token
$fabricHeaders = @{
'Content-Type' = "application/json"
'Authorization' = "Bearer {0}" -f $fabricToken
}
}
# Set the headers
SetFabricHeaders
# Build url for retrieving artifacts API
$artifactUrl = "https://api.powerbi.com/v1.0/myorg/pipelines/$pipelineId/stages/$testWorkspaceStage/artifacts"
# Retrieve all artifacts
$artifacts = Invoke-RestMethod -Headers $fabricHeaders -Uri $artifactUrl -Method GET
# Define orphaned reports for deletion
$orphanedReports = (
$artifacts.reports |
Where-Object { [string]::IsNullOrEmpty($_.sourceArtifactId) } |
Select-Object -ExpandProperty artifactId
)
# Build url for item deletion API
$itemUrl = "https://api.fabric.microsoft.com/v1/workspaces/$testWorkspaceId/items/"
# Iterate through all orphaned reports and delete them
foreach ($report in $orphanedReports) {
# Construct API call for each orphaned item
$url = "$itemUrl/$report"
Write-Host "Deleting report with ID: $report"
# Make the web request
try {
Invoke-RestMethod -Headers $fabricHeaders -Uri $url -Method DELETE
Write-Host "Report with ID $report deleted"
}
catch {
Write-Host "Error making request for ID $report : $_"
}
}
The other possible orphaned items (notebooks, data pipelines, lakehouses etc) can be deleted using the DELETE Items endpoint based on the displayName (as those have to be unique for these items in a workspace).
Hope this helps someone out in the future as well 👍
PS: Since this deletes only reports (Power BI items), it means that the script can also be called by a service principal.
*edit: I have no idea how I didn't see the "List Deployment Pipeline Stage Items" Fabric REST API before (link here) but with that you can solve the entire problem of orphaned items (not just reports, not just based on displayName (since those can differ even when the items are still paired), but everything that is no longer paired).
This is also the solution to the problem that you get when you have an item, you deploy that to test, remove it from dev, and create it back in dev with the same name. Those actions cause your deploy to fail because it is trying to deploy an item to test eventhough there is already an item (unpaired) in test with that name. If you run the script first using the Fabric API, followed by a deploy, this issue will no longer occur.
Of course I do also have the Powershell script for this better way of working:
$testStageName = "Test"
$pipelineName = "<YOUR PIPELINE NAME>"
$resourceUrl = "https://api.fabric.microsoft.com"
$fabricHeaders = @{}
function SetFabricHeaders() {
# Set default subscription
Update-AzConfig -DefaultSubscriptionForLogin $subscriptionId
# Login to Azure
Connect-AzAccount | Out-Null
# Get authentication
$fabricToken = (Get-AzAccessToken -ResourceUrl $resourceUrl).Token
$fabricHeaders = @{
'Content-Type' = "application/json"
'Authorization' = "Bearer {0}" -f $fabricToken
}
}
SetFabricHeaders
# Get the pipelineId of the deployment pipeline
$deploymentPipelineUrl = "https://api.fabric.microsoft.com/v1/deploymentPipelines"
$deploymentPipeline = (Invoke-RestMethod -Headers $fabricHeaders -Uri $deploymentPipelineUrl -Method GET).value
$deploymentPipelineId = ($deploymentPipeline | Where-Object { $_.displayName -eq $pipelineName}).id
# Get the stageId and workspaceId of the test stage
$stageUrl = "https://api.fabric.microsoft.com/v1/deploymentPipelines/$deploymentPipelineId/stages"
$stages = (Invoke-RestMethod -Headers $fabricHeaders -Uri $stageUrl -Method GET).value
$testStage = $stages | Where-Object { $_.displayName -eq $testStageName }
$testStageId = $testStage.id
$testWorkspaceId = $testStage.workspaceId
# Retrieve the orphaned items for deletion
$itemUrl = "https://api.fabric.microsoft.com/v1/deploymentPipelines/$deploymentPipelineId/stages/$testStageId/items"
$itemList = (Invoke-RestMethod -Headers $fabricHeaders -Uri $itemUrl -Method GET).value
$orphanedItems = $itemList |
Where-Object { [string]::IsNullOrEmpty($_.sourceItemId) }
# Build url for item deletion API
$itemUrl = "https://api.fabric.microsoft.com/v1/workspaces/$testWorkspaceId/items/"
# Build an array to store the display names of items that were not deleted
$errorDetails = @()
# Iterate through all orphaned items and delete them
foreach ($item in $orphanedItems) {
$itemId = $item.itemId
$itemName = $item.itemDisplayName
# Construct API call for each orphaned item
$url = "$itemUrl/$itemId"
Write-Host "Deleting item with ID: $itemId"
# Delete orphaned items
try {
Invoke-RestMethod -Headers $fabricHeaders -Uri $url -Method DELETE
Write-Host "item with ID $itemId deleted"
}
catch {
Write-Host "Error making request for ID $itemId : $_"
$errorDetails += [PSCustomObject]@{
ItemName = $itemName
}
}
}
The script now also contains an array that logs the name of the items that fail to be deleted. This is because items that have dependencies built on them (if a notebook is used in a data pipeline for instance) can not be deleted. Until I find a way to deal with that issue in code (and have the time to look into it), I will only log the item name and delete the dependency by hand.
Hope this helps 👍
