Get data from Power BI service with PowerShell Cmdlets

We had some cases where we would like to check what information was within the Power BI service and be able to make a decision in the BI area, based on this data.

Therefore, one way we found to access this data and be able to work it in a database or even within an Excel spreadsheet, was extracting the data through a script in PowerShell and exporting it in a CSV file.

I’m not a PowerShell expert, but after looking at forums, official documentation and even talking to Microsoft support, I managed to solve the problem we had with the solutions below. So if there is any easier way to complete this proposed solution or good practices that have not been carried out, I apologize in advance.

So, before showing the PowerShell script, it is necessary that the user who will access the service must be an admin user in order to have access to the data without restriction.

To run the PowerShell script it will also be necessary to install the PowerBI modules available at the link below, if the module is not installed, it will not be possible to run the script successfully. Preferably install the module “MicrosoftPowerBIMgmt.Admin”, which is the Admin module for Power BI Cmdlets, but it’s necessary to install the modules for each problem.

After installing the module successfully, you should save the script below and run it in PowerShell.

The first script, obtains the data using the module “Get-PowerBIWorkspace”, and its function is to return a list of Power BI workspaces, all documentation can be found in the link below as well.

For the second script, obtains the data using the module “Get-PowerBIWorkspace”, and its function is to return a list of Power BI workspaces, all documentation can be found in the link below as well. And it is worth noting that the workspace is the maximum hierarchy and then we have datasets, then datasources, etc. So to get a dataset, we need to know which workspace it belongs to.

To get a datasource, we need to know which workspace it belongs to and also which dataset it pertains to, and so on.

In this third script, we will access the workspace and dataset using the ClientID, and this ClientID is obtained through an application created in Azure according to the link below, the process of this script is the same, but in different steps:

Thas it! We can now obtain Power BI service data in a variety of ways, through PowerShell using Cmdlets commands.

The script for this post can be found on my github (@guimatheus92).

Mechanical Engineer and developer in the Business Intelligence area, passionate about technology, I have knowledge and experience to create a BI architecture.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store