Hits: 12378
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.
Addendum 21.06.2018:
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.
Happy Querying!
to get this to work you’ll need to add these lines at the top of the vba code:
Dim adClipString As Integer
adClipString = 2
Thank you for your input.
We tested the code on several machines without any issues.
Did the vba not work for you?
Anyway, I wrote an addendum at the end of the post.
Thank you again!
Excellent!!!!
I Modified to it work at vbscript without parameters and it works perfectly, good job
yo made me so happy,
where you find the documentation, for vba
thank you
After change my Azure Password, the VBA code return a “The Open method of ‘_Recordset’ object failed”. I tried to add square brackets to the str_command’s column names but had the same response.
Before the password change, just did as @’Gage Renzi’ mentioned and worked fine. Just causes an error when it tries to import a measure table, but stills import the other ones.
Actually, the .ocd file cannot be downloaded from Power BI Online Services anymore it is downloaded the xlsx file instead.
You can find the connection string by going to data > Connections > Properties > Definition > Connection string on the downloaded xlsx file.
The Provider is MSOLAP.8 instead of MSLOAP.7 when this comment was posted, and if you replace the VBA connection string by this new one, the code runs fine.
I did not change only the Provider field but the entire connection string since i just need the dataset of a specific report. Even though this workarround made part of the VBA code uselass, this VBA solution is awesome so thx @’Hans Peter Pfister’.