cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pfister Established Member
Established Member

Export datasets from Power BI Service to CSV using VBA

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.

 

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.

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.

Location:
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“.

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.

Dataset_ID

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

image

and then I wrote some VBA code:

VBA_Part1VBA_Part2VBA_Part3

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!

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com
1 REPLY 1
Highlighted
Super User
Super User

Re: Export datasets from Power BI Service to CSV using VBA

Thanks for sharing

 

Regards

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 157 members 1,687 guests
Please welcome our newest community members: