Export Data from Power BI Service does not export all the underlying data

[jeaninnes]

Hi there, 

I have a report that shows 4 columns.

Actuals (table1)

Forecast (table1)

Opportunity Forecast (table2)

Budget (table3)

The relationships are set between the tables - basically both table1 & table2 join to table3 to compare to the budget.  The chart works well & I can see the underlying data records when I interact with the report.  

My users would like to be able to export the entire data list behind the report so that they can build other reports.   The report has been published to the power BI service.

However when the users do this, it appears to only extract data from table1 and table3 - > so I can't see the opportunity forecast values.

How can I correct this?  I just want to show all the data that makes up the report and it appears I can't do this.

[GilbertQ]

Hi @jeaninnes

From my understanding when you export the data from a visual it simply exports the data that makes up the visual.

If you users want access to the dataset I would suggest using the Power BI Publisher for Excel. This will enable them to connect to the dataset, and they can then leverage this to get the data that they require.

https://powerbi.microsoft.com/en-us/excel-dashboard-publisher/

[jeaninnes]

Hi @guavaq, 

thanks for the reply.  I've attempted to use this - but it's not working for me.

To provide a little more context, my powerBI report is connecting to a Dynamics365 datasource.  I'm not sure if this then makes it more difficult to view the dataset underneath. 

When I export the summary data, I get the 4 data columns I'm after, but when I export the underlying results, I don't get that 4th column.

It appears to me that it's not recognising the whole data relationship or something like that.

best regards, 

Jean

[v-huizhn-msft]

Hi @jeaninnes,

When you click the elliplses on the right top of visual->show data, the 4th column has no data? And what's meaning of "export the summary data"? Could you please share more details?

Best Regards,
Angelia

[jeaninnes]

Hi Angelia, 

Steps to reproduce:

1. Chart published to power BI service.

2. Chart contains 4 columns of data.  These come from 3 tables that are joined via relationships.

3. Elipses: Show Data works fine.  I can see my 4 columns & their amounts.

4. Elipses: Export Data : Summarised Data also works correctly.  I get an excel spreadsheet with the column amounts as I would expect.

5. Elipses: Export Data : Underlying Data does not give me all the underlying data.  I will post screen shots.  It appears to give me data from two of the tables, but not the third.  My users want to be able to have a table with all the underlying data that totals to give the summary information that I'm displaying - but it looks like it will only do this IF your underlying data is all in the one table OR in a maximum of two tables with a re;ationship join.  

[jeaninnes]

these are the headers for my summary data - which is working as I would expect.  

When I export the underlying data, I don't see any OppyForecastRevenue columns at all.  So there's no way to see what is contributing to these summary totals.

[v-huizhn-msft]

Hi @jeaninnes,

Got it. After research, there are some limitations and considerations for Export Date, please review the part in this article. Please check your report or dataset has items considered in the limitations.

• When using DirectQuery, the maximum amount of data that can be exported is 16 MB. This may result in exporting less than the maximum number of rows, especially if there are many columns, data that is difficult to compress, and other factors that increase file size and decrease number of rows exported.

• Power BI only supports export in visuals that use basic aggregates. Export is not available for visuals using model or report measures.

• Custom visuals, and R visuals, are not currently supported.

• Export data is not available for users outside of your organization who are using a dashboard that has been shared with them.

• If there is unicode character in the .csv file, the text in Excel may not display properly. Although, opening it in Notepad will work fine. Examples of unicode characters are currency symbols and foreign words. The workaround for this is to import the csv into Excel, instead of opening the csv directly.

---

 
Best Regards,
Angelia

[franck_axires]

Hi @jeaninnes 

Check if you have measures stored in a dedicated table and not in the table where the data they use resides.

For some reason, trying to export data from a visual that uses measures will only export the aggregate data if the measures' home table is not where their data resides.

If this is the case, just move them back to their data table and the underlying data export will work just fine.

Hope the PBI Team finds a way soon to let us visually group measures as this is much more convenient than have them spread around the fact & dimension tables alphabetically...