Export information about Power BI tenant assets to Excel or CSV files

ExportPBIServiceAssets-toCSV.PS1

<##################################################################################################>
<#
    Export information about assets in the Power BI Tenant to individual CSV files.
#>
<##################################################################################################>
<# 
NOTE: You have to be a Power BI Admin to use the -Scope Organization parameter option. #>

<# First, get all the capacities for the tenant, if they have any #>
Get-PowerBICapacity -Scope Organization | 
Export-Excel -Path c:\temp\PBIServiceAssets-Capacity.CSV -WorksheetName Capacity

<# Next, collect info about all the Workspaces #>
Get-PowerBIWorkspace -Scope Organization -All | 
Export-Excel -Path c:\temp\PBIServiceAssets-Workspaces.CSV -WorksheetName Workspaces

<# Collect all of the Datasets across the tenant and append the WorkspaceID they belong to. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All) 
{
    $Dataset = Get-PowerBIDataset -WorkspaceId $Workspace.Id -Scope Organization 
    $Dataset | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $Dataset | 
    Export-Excel -Path c:\temp\PBIServiceAssets-Datasets.CSV -WorksheetName Datasets -Append
}

<# Collect all of the Reports across the tenant and append the WorkspaceID they belong to. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All ) 
{
    $Dataset = Get-PowerBIReport -WorkspaceId ($Workspace.Id) -Scope Organization
    $Dataset | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $Dataset | 
    Export-Excel -Path c:\temp\PBIServiceAssets-Reports.CSV -WorksheetName Reports -Append
}

<# Collect all of the dataflows across the tenant and append the WorkspaceID they belong to. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All | WHERE { $_.Type -eq 'Workspace'}) 
{
    $Dataflow = Get-PowerBIDataflow -WorkspaceId $Workspace.Id -Scope Organization 
    $Dataflow | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $Dataflow | 
    Export-Excel -Path c:\temp\PBIServiceAssets-dataflows.CSV -WorksheetName dataflows -Append
}

<# Collect all the Datasources of the dataflows across the tenant and append the WorkspaceID they belong to.
NOTE: There is a bug in the API that causes this step to time out on dataflows which have never been 'refreshed'. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization | WHERE { $_.Type -eq 'Workspace'} ) 
{
    $DataflowDatasource = Get-PowerBIDataflow -WorkspaceId $Workspace.Id -Scope Organization | Get-PowerBIDataflowDatasource -WorkspaceId $Workspace.Id -Scope Organization
    $DataflowDatasource | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $DataflowDatasource | 
    Export-Excel -Path c:\temp\PBIServiceAssetsdataflowDatasources.CSV -WorksheetName dataflowDatasources -Append
}

ExportPBIServiceAssets-toExcel.PS1

<##################################################################################################>
<#
    Export information about assets in the Power BI Tenant to worksheets in Excel.
    If you prefer to use .CSV files, jump down to the next file in this gist.
#>
<##################################################################################################>
<# Export results to Excel (requires the ImportExcel module from PowerShell Gallery)
NOTE: You have to be a Power BI Admin to use the -Scope Organization parameter option. #>

<# First, get all the capacities for the tenant, if they have any #>
Get-PowerBICapacity -Scope Organization | 
Export-Excel -Path c:\temp\PBIServiceAssets.xlsx -WorksheetName Capacity

<# Next, collect info about all the Workspaces #>
Get-PowerBIWorkspace -Scope Organization -All | 
Export-Excel -Path c:\temp\PBIServiceAssets.xlsx -WorksheetName Workspaces

<# Collect all of the Datasets across the tenant and append the WorkspaceID they belong to. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All) 
{
    $Dataset = Get-PowerBIDataset -WorkspaceId $Workspace.Id -Scope Organization 
    $Dataset | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $Dataset | 
    Export-Excel -Path c:\temp\PBIServiceAssets.xlsx -WorksheetName DatasetsIn_Workspaces -Append
}

<# Collect all of the Reports across the tenant and append the WorkspaceID they belong to. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All ) 
{
    $Dataset = Get-PowerBIReport -WorkspaceId ($Workspace.Id) -Scope Organization
    $Dataset | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $Dataset | 
    Export-Excel -Path c:\temp\PBIServiceAssets.xlsx -WorksheetName ReportsIn_Workspaces -Append
}

<# Collect all of the dataflows across the tenant and append the WorkspaceID they belong to. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All | WHERE { $_.Type -eq 'Workspace'}) 
{
    $Dataflow = Get-PowerBIDataflow -WorkspaceId $Workspace.Id -Scope Organization 
    $Dataflow | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $Dataflow | 
    Export-Excel -Path c:\temp\PBIServiceAssets.xlsx -WorksheetName dataflows -Append
}

<# Collect all the Datasources of the dataflows across the tenant and append the WorkspaceID they belong to.
NOTE: There is a bug in the API that causes this step to time out on dataflows which have never been 'refreshed'. #>
foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization | WHERE { $_.Type -eq 'Workspace'} ) 
{
    $DataflowDatasource = Get-PowerBIDataflow -WorkspaceId $Workspace.Id -Scope Organization | Get-PowerBIDataflowDatasource -WorkspaceId $Workspace.Id -Scope Organization
    $DataflowDatasource | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;
    $DataflowDatasource | 
    Export-Excel -Path c:\temp\PBIServiceAssets.xlsx -WorksheetName dataflowDatasources -Append
}