In today’s post, we will discuss how to get metadata for a requested list of Workspaces using the Power BI Scanner API. The result shows Data sources and connection details for each Workspace, Data sources used by each Dataset, and Reports connected to each Dataset. For the Datasets, you can also extract metadata like tables, columns, measures, DAX expressions, mashup queries, and so on.

To accomplish this, we will use 4 Scanner APIs:

• GetModifiedWorkspaces
• WorkspaceGetInfo
• WorkspaceScanStatus
• WorkspaceScanResult

The user must be an administrator to run the APIs. These APIs support both public and sovereign clouds. Also, if you are using a service principal, there must be No Power BI admin-consent-required permissions set on the app. For this example, we do not use a service principal.

Let’s walkthrough the steps:

Enable the metadata scanning in Power BI Admin portal.

Step one: Sign in to Power BI via PowerShell as an Admin and return list of Workspace IDs:

Step two: Use the Workspace IDs from step one to make the scan API call:

Step three: Get the scan status using the ID from step two result:

Step four: Gets the scan result for the specified scan using the same scan ID from step two or three:

This will return the metadata in JSON format, you can then use the file as a datasource to create Audit log report in Power BI.

Scripts:

#Log in to Power BI as an Admin

Login-PowerBI

# Get list of Workspace IDs in the Organization

$url = ” https://api.powerbi.com/v1.0/myorg/admin/workspaces/modified“

Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json

# Make API metadata Scan call

$body =

‘{

  “workspaces”: [

  “Add Workspace ID here”,

  “Add Workspace ID here”

  ]

}’

$url= ” https://api.powerbi.com/v1.0/myorg/admin/workspaces/getInfo?lineage=True&datasourceDetails=True&datasetSchema=True&datasetExpressions=True“

Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body | ConvertFrom-Json

# Check the scan Status

 $scanID = “Add Scan ID here “

 $url = ” https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanstatus/” + $scanID

 Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json

 

#Get the scan result for the specified scan

 $scanID = “Add Scan ID here”

 $url = ” https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanResult/” + $scanID

  Invoke-PowerBIRestMethod -Url $url -Method Get 

I also came across a very helpful post by Power BI Tips that uses Power Automate Flow to handles the entire API request and call.

Happy Power BI Administration 🙂

---