Forum Discussion

learning_dax's avatar
learning_dax
Helper II
3 years ago

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!

Need Help

1 Reply

  • Joschko's avatar
    Joschko
    Helper I
    3 years ago

    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.

    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.