cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Export of Data from PBI Online Service is Incomplete

Hello,

I was hoping someone is able to help me on this one, as my searches haven't yeidled any helpful answers.

 

I have a simple bar chart within a report hosted on PowerBI Online.

 

The chart contains 2xseries of data:

  1. Booked_FTE between 3-Jun-17 and 15-Jul-17
  2. Forecast_FTE between 15-Jul-17 and 18-Nov-17

The graphic is shown as below.

Booked&Planned ExampleBooked&Planned Example

 

Unfortuantely, when users "Export Data" from the elipses in the top right, only the Booked_FTE series of data is included in the export file.

 

Both series are a "calcualted" column based on a simple divide function, summarised as a SUM.  Field structure of the visualisation is show below.

Booked&Planned Field StructureBooked&Planned Field Structure

 

I can't think of or find a sensible reason as to why the full dataset used to render the graphic is not included in the data export.

 

Can anyone shed some light or provide guidance on how to address this issue??

 

Thanks.

 

Braden.

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Export of Data from PBI Online Service is Incomplete

Hi @waltonb,

 

There is an option "See Data" below the option "Export Data". Did you try it? How does the data look like in the Service?

Due to the limitations of "Export Data", could you please post the formula of these items (measure and calculated column)?

>>The list below is from the official document: powerbi-service-export-data

  • The maximum number of rows that can be exported to .csv is 30,000.

  • The maximum number of rows that can be exported to .xlsx is 150,000.

  • 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.

  • 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. To do this:

    1. Open Excel
    2. From the Data tab, select Get external data > From text.
  • Power BI admins have the ability to disable the export of data.

 

Export of Data from PBI Online Service is Incomplete.jpg 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Export of Data from PBI Online Service is Incomplete

Hi Dale,

"See data" shows both value series as well as the two tool tips as expected, at the sumamry level.

 

I am aware of the row limitations of the export, but the Booked_FTE is approx. 45,700 rows, so there is "space" for the second series.

 

If the Forecast_FTE row count exceeds the remaining available rows, will it truncate the data upto 150,000 rows, or exclude it as it can't "fit" into the remaining available rows??

 

There are no measures on this standard PBI bar chart.  Formula for the calculated columns are as follows:

  • _Booked_FTE = divide(_LIVE_WP_BookedHrs_16w[SumOfLabourHours],_LIVE_WP_BookedHrs_16w[_AveHrsPerWeek])
  • _Forecast_FTE = divide(_LIVE_P6_L3_Histogram_Daily[PeriodHours],_LIVE_P6_L3_Histogram_Daily[Average_hrs_week])

Cheers.

Braden.

Highlighted
Microsoft
Microsoft

Re: Export of Data from PBI Online Service is Incomplete

@waltonb,

 

Hi Braden.,

 

I have tested it in many scenarios. I can't reproduce the issue. Is the "Forecast_FTE" 0 when "Booked_FTE" has a meaning value? Could you please post a snapshot of the summarized data?

BTW, exporting will truncate the records that exceed the maximum according to my test.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Export of Data from PBI Online Service is Incomplete

 

Hi Dale,

 

There is a slight difference in my data compared to your sample data.  The _Booked data is historical data only, _Forecast is future data only...there is no booked hours in the time periods where there is forecast hours.

 

 

Refer image below.

Booked&Planned SeeDataViewBooked&Planned SeeDataViewBooked&Planned SeeDataViewBooked&Planned SeeDataView

 

 

In your data, there appears to be "0" values in date ranges where there is no data.  In my data, I will have blank() values, i.e., no data.

 

Could the treatment of blank() values influence the data extraction??

 

Thanks.

 

Braden.

Highlighted
Microsoft
Microsoft

Re: Export of Data from PBI Online Service is Incomplete

@waltonb,

 

Hi Braden,

 

I have tried these scenario below.

1. RLS;

2. Apps workspace;

3. Member of an App or not;

4. Hide the column in the dataset.

 

Which scenario is yours? I can't reproduce this issue. I wonder if you could share your file with me. But most of the time I think the answer is no.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors