Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Helper I
Helper I

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors