Downloading a Power BI Dataset with VBA wasn’t really a thing I planned to do until Microsoft released the new PBI Usage datasets. In this article, I’ll describe the steps to export datasets from Power BI Service to CSV using VBA.
Classic download options
If you want to download a dataset from Power BI Service (PBIS), you have several options at your hands:
- Analyze in Excel
- Download the PBIX File
- Connect your PBI Desktop to the PBIS
But you can’t enrich the data in none of these options. This isn’t really an issue as long the data source is in your control.
With the introduction of the usage datasets, this particular dataset is out of your control. You can measure the usage of just one workspace but you can’t build a report of the usage across all workspaces.
I searched for a possibility to build one single usage report across all workspaces and came up with this VBA solution.
Yes, the good old VBA, the most hated programming language in the world. I’m pretty sure that one can achieve the same goal with C# or VB, but most Excel Cracks out there know VBA.
As there is no chance to manipulate the connection string for the usage dataset in Power BI Desktop, I downloaded a connection string from PBIS using “Analyze in Excel”. In this article https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/ you’ll find everything you need to do so.
Open the downloaded .odc file and enable the connection.
After enabling the connection you’ll get a pivot table layout.
Unfortunately, you can’t alter or enrich the data. Wouldn’t it be nice if you could?
But as long as you can’t manipulate the connection string there will be no success.
Being at this point I started wondering, if there is a chance to alter the connection string. So I opened the .odc file in Notepad.
Editing the query
To achieve my goal, I had to understand the meaning of the different parts in the connection string.
I had to provide the location in the format “north-europe”.
The value for the dataset is unique for every PBIS dataset. You can find the value in the PBIS url of the usage report.
The original connection string comes up with “Integrated Security = ClaimsToken”. After changing this part of the string to “Integrated Security = SSPI”, it worked.
That’s all. Really? Almost
VBA Code to download the PBIS dataset
My goal is to export the data directly to .csv files. By writing some lines of VBA Code, it turned out to be really easy.
First I defined three tables in Excel.
and then I wrote some VBA code:
The code connects to PBIS and gets the data of all tables of the defined datasets and writes the data directly into csv files. One for each dataset. You may download the code Get_PBI_usage_metrics.
Please let me know, if you find any errors in the code.
Gage Renzi pointed out to me that the code didn’t work for him. If this is also the case for you, then add the following two lines to the code.
Dim adClipString As Integer
adClipString = 2
Many thanks to Gage Renzi for the input.
Want to know more about Power BI or Power Query?
Take a look here.