cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
learning_dax
Helper II
Helper II

Sharing Power BI Data/Datasets

Hi all,

 

I have different reports, dataflows, and datamarts in the PBI service and all have worked beautifully for the users who are in the Power BI ecosystem that want to build reports. Now I am having difficulties with those who use other visualization platforms, Excel, Smartsheets, etc for their analysis. They do not want to use my visualizations they simply need the data behind these Power BI reports. Is there a way I can share this in some type of Excel format? API to connect to the dataset? Automated Export? 

 

What is the best way to make these datasets for reports, dataflows, etc. share-able across the organization for those outside of Power BI?

 

Thanks a lot!

1 REPLY 1
Joschko
Frequent Visitor

Hey,

don't know if this fits for you.

Our user who are only interested in data pulling it directly into their Excel.

First step is to create a connection in Excel with the Analyze in Excel option from the PBI Service.

Check Queries and Connections in Excel to fetch the name of the connection.

Open VBA Editor with ALT+F11. Select Insert, Module and paste this VBA Code.

 

 

Public Function GetPowerBIData(ConnectionName As String, DaxStatement As String) As Variant

    On Error GoTo errhandler

    Set conn = CreateObject("ADODB.CONNECTION"): Set rec = CreateObject("ADODB.RECORDSET")
   
    conn.Open Mid(ThisWorkbook.Connections(ConnectionName).OLEDBConnection.Connection, 7)
    rec.Open DaxStatement, conn
    
    arr = rec.GetRows
    GetPowerBIData = Application.WorksheetFunction.Transpose(arr)

    Exit Function
    
errhandler:
    GetPowerBIData = Err.Description
End Function

 

 

Now your User can pull any data from the Dataset by using this function.

Joschko_0-1682441198949.png

The users building DAX statements with the help of Performance analyzer in PBI Desktop.

This works only with an Excel supporting Dynamic Arrays.

Hope this will help you.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors