Skip to content

Instantly share code, notes, and snippets.

@SQLvariant
Created September 20, 2019 15:40
Show Gist options
  • Save SQLvariant/f0f55685e954848a19fcadd35e29f85c to your computer and use it in GitHub Desktop.
Save SQLvariant/f0f55685e954848a19fcadd35e29f85c to your computer and use it in GitHub Desktop.
Export information about Power BI tenant assets to Excel or CSV files
<##################################################################################################>
<#
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
}
<##################################################################################################>
<#
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment