Export datasets from Power BI Service to CSV using VBA

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:

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.

Pivot table

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.

Connection String

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.Connection String

Editing the query

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

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

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 Winking smile

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!

5 thoughts on “Export datasets from Power BI Service to CSV using VBA

  • 21. June 2018 at 17:15

    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

    • 21. June 2018 at 17:21

      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!

  • 27. May 2020 at 16:59

    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

  • 25. July 2020 at 19:51

    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.

    • 25. July 2020 at 21:21

      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’.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: