PowerShell Notebook (open in Azure Data Studio) to help you export all of your Power BI Workspace Asset information.

ExportPowerBIWorkspaceAssets_withPowerShell.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "<img src=\"https://app.powerbi.com/13.0.10537.111/images/PowerBI96x96.png?raw=true\" width=\"10%\"> ➕\r\n",
                "<img src=\"https://github.com/Microsoft/azuredatastudio/blob/master/samples/notebookSamples/Graphics/AzureDataStudioLogo.png?raw=true\" width=\"10%\"> = ❤\r\n",
                "\r\n",
                "# Export Power BI Workspace Assets with PowerShell\r\n",
                "\r\n",
                "This PowerShell Notebook will help you collect information about the following types of Power BI assets, from the Power BI service:\r\n",
                "\r\n",
                "+ Workspaces\r\n",
                "+ Datasets\r\n",
                "+ Reports\r\n",
                "+ Power BI dataflows\r\n",
                "+ Data Sources of those Power BI dataflows\r\n",
                "\r\n",
                "NOTE: This Notebook exports all the this information to different Worksheets within the same Excel file.  In order to do this, we use the community-driven `ImportExcel` module from the PowerShell Gallery.  Also of note, the name of the Excel file we are exporting to is named `c:\\temp\\PBIServiceAssets.xlsx` but you may want to change that."
            ],
            "metadata": {
                "azdata_cell_guid": "e6a64804-8f8f-42d9-bf18-40eb663e83fd"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Install-Module MicrosoftPowerBIMgmt"
            ],
            "metadata": {
                "azdata_cell_guid": "a12574a0-1897-4433-ad7c-f3a969751934"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Before you can do anything with any of the Power BI cmdlets, you must first login to the Power BI Service."
            ],
            "metadata": {
                "azdata_cell_guid": "810ade90-1d85-43b3-8af1-551f6e7570a8"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Login-PowerBI"
            ],
            "metadata": {
                "azdata_cell_guid": "98cb77df-9123-46b0-bcb6-18193dda3d60"
            },
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "First, collect info about all the **Workspaces**, across your entire tenant.\r\n",
                "\r\n",
                "NOTE: You must be a Power BI Admin in order to successfully execute the code below.  If you are not a Power BI Admin, remove the `-Scope Organization` portion of the code below, as well as all subsequent commands."
            ],
            "metadata": {
                "azdata_cell_guid": "7d93a09e-1b20-47b9-bbd4-a1a4b73b7cef"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-PowerBIWorkspace -Scope Organization -All |\n",
                "Export-Excel -Path C:\\temp\\PBIServiceAssets.xlsx -WorksheetName Workspace"
            ],
            "metadata": {
                "azdata_cell_guid": "ca8d9968-4e3e-4c96-847f-12dae7425685"
            },
            "outputs": [],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect all of the **Datasets** across the tenant and append the WorkspaceID they belong to."
            ],
            "metadata": {
                "azdata_cell_guid": "acac7604-8766-4fad-851c-8e3af6fa4dae"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All) \r\n",
                "{\r\n",
                "    $Dataset = Get-PowerBIDataset -WorkspaceId $Workspace.Id -Scope Organization \r\n",
                "    Export-Excel -Path c:\\temp\\PBIServiceAssets.xlsx -WorksheetName DatasetsIn_Workspaces -Append\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "7eb2a35d-9270-4154-b98c-fe33938c5253"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect all of the **Reports** across the tenant and append the WorkspaceID they belong to."
            ],
            "metadata": {
                "azdata_cell_guid": "789770a4-5066-4867-993c-ad0d2de87ae9"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All ) \r\n",
                "{\r\n",
                "    $Dataset = Get-PowerBIReport -WorkspaceId ($Workspace.Id) -Scope Organization\r\n",
                "    $Dataset | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;\r\n",
                "    $Dataset | \r\n",
                "    Export-Excel -Path c:\\temp\\PBIServiceAssets.xlsx -WorksheetName ReportsIn_Workspaces -Append\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "815cb7da-2c8a-4834-ac60-d52afa726453"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect all of the **dataflows** across the tenant and append the WorkspaceID they belong to."
            ],
            "metadata": {
                "azdata_cell_guid": "eff2e89f-fe60-4750-94f0-f03d8af05f78"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization -All | WHERE { $_.Type -eq 'Workspace'}) \r\n",
                "{\r\n",
                "    $Dataflow = Get-PowerBIDataflow -WorkspaceId $Workspace.Id -Scope Organization \r\n",
                "    $Dataflow | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;\r\n",
                "    $Dataflow | \r\n",
                "    Export-Excel -Path c:\\temp\\PBIServiceAssets.xlsx -WorksheetName dataflows -Append\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "945c845b-46cc-4d2c-aadf-051593f72934"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect all the **Datasources** of the **dataflows** across the tenant and append the WorkspaceID they belong to.\r\n",
                "NOTE: There used to be a bug in the API that causes this step to time out on dataflows which have never been 'refreshed'.  I think it has been fixed, but please let me know if you're still running into it?"
            ],
            "metadata": {
                "azdata_cell_guid": "90ff6f98-6b10-43df-8513-ab334c902361"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "foreach ($Workspace in Get-PowerBIWorkspace -Scope Organization | WHERE { $_.Type -eq 'Workspace'} ) \r\n",
                "{\r\n",
                "    $DataflowDatasource = Get-PowerBIDataflow -WorkspaceId $Workspace.Id -Scope Organization | \r\n",
                "    Get-PowerBIDataflowDatasource -WorkspaceId $Workspace.Id -Scope Organization\r\n",
                "    $DataflowDatasource | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $Workspace.Id;\r\n",
                "    $DataflowDatasource | \r\n",
                "    Export-Excel -Path c:\\temp\\PBIServiceAssets.xlsx -WorksheetName dataflowDatasources -Append\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "ffa0705f-265d-47b2-9ca9-d594a5a0913b"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "## You're done.  Go import your data 👍\r\n",
                "\r\n",
                "If you're interested in automating this.  You can automate PowerShell scripts with a SQL Server Agent Job."
            ],
            "metadata": {
                "azdata_cell_guid": "a2ad3df3-6d95-42f6-a3ea-2186e3474dcb"
            }
        }
    ]
}