Export datasets from Power BI Service to CSV using VBA

[Anonymous]

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.

 

Prerequisites

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.

I changed the format of the string a little bit for better readability. The most interesting part of the string are the yellow and the brown ones.

 

Editing the query

To achieve my goal, I had to understand the meaning of the different parts in the connection string.

Location:
You have to know the Azure location of your dataset. To find the location, just press in PBIS and you’ll find your Azure location in „Info“. For my company, it’s Northern Europe.

I had to provide the location in the format „north-europe“.

Dataset:
The value for the dataset is unique for every PBIS dataset. You can find the value in the PBIS url of the usage report.

Security:
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.

Want to know more about Power BI or Power Query? Take a look here.

Happy Querying!

[Anonymous]

Did as you described but found an error on GetString method. It says that adClipString is an undefined variable. When i replaced "adClipString" for "2", the value of the adClipString StringFormatEnum , it worked for a while. But recently it pops a "Method 'Open' of object '_Recordset' failed" error when i run the macro. Thx @Anonymous for the amazing solution btw!

[TomMartens]

Thanks for sharing

Regards

Tom