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?
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)
GetPowerBIData = Err.Description
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.