Mismatch with no of records exported and no of records displayed

[SatyaSunitha1_]

Hi,

The no. of records exported and no of records displayed are not matching. Please need your help on this ASAP.

To display the no. of records below is the measure I used - 

No of Rows = "No of Records - "&COUNTA('Data PL Level'[Metric Type])

FYI-

- The  field "Metric Type" doesn't have a blank record/cell.

- All the fields in the table visual are from single table.

- The field used in the measure is from the same table.

- No filters applied in Visual, Page, Report and Drillthrough level filters.

Regards,

Satya

[bs9694]

Hi,

The problem mentioned by the requester is correct. I too faced the same issue. From PBI Service I am unable to export all the record counts. I am exporting data from a table visual.  While exporting data the record count is 41081 for a particular year but after exporting data into csv or excel the record counts exporting are only ~35000.

There is no concrete resolution to this given anywhere even though this issue is raised from last 4 yrs by users.

[quentin_vigne]

Hi @SatyaSunitha1_

Maybe you should use the count option of PowerBI, you will have the ability to choose if the count is distinct or not, and It will be easier. 

Just add a card box for Metric Type and select Count. It should match

Quentin

[SatyaSunitha1_]

Hi Quentin,

As per your suggestion, added the card box for Metric type and selected "Count" option. Still the no of records is same as I used in the measure i.e 31,254 records,

For confirmation, I also run the query in SQL database to see the no of records where my data exists. The no. of records are matching with the number in both the option (measure/Count option) i.e. 31,254

But after I export from PBI service, the no of records are 22,931 records.

Regards,

Satya

[SatyaSunitha1_]

Hi Quentin,

I see 22,931 records after exporting the data from PBI service to excel.

Regards,

Satya

[SatyaSunitha1_]

Hi Quentin,

I see only 2 records if i choose Count (Distinct), because in "Metric Type" filed I have only two categories mentioned below

1. Upfront

2. Order

Regards,

Satya

[quentin_vigne]

@SatyaSunitha1_

I see.

Maybe the export option doesn't export every value from PBI ? Because if the cardbox display the same count than your sql query, it's because that's the right count.

If you don't have the same count on excel, maybe the export filter the data ? 

Can you try to check wich data are missing ? (don't know if you can do that)

Else, maybe you can share your data with us ? (export your data in a csv file if it's not sensitive data and we will try to check if there is a problem)

[SatyaSunitha1_]

Hi Quentin,

Yes, export option is not exporting all the values.

I can not share the data as it is sensitive. Any action frrom your team ?

Regards,

Satya

[quentin_vigne]

If you export these data in a csv file and open it in notepad, how many records do you have ? 

[SatyaSunitha1_]

Hi Quentin,

Still same records 22,931

Regards,

Satya

[quentin_vigne]

From where (in PowerBI) are you exporting the data ?

[SatyaSunitha1_]

Hi Quintin,

By clicking on ... elipses option on table visual (right side top corner).

Regards,

Satya

[quentin_vigne]

@SatyaSunitha1_

Are you sure there is 0 filters, 0 Row Level Security and NO interaction from other visuals on the table visual ? 

[SatyaSunitha1_]

Hi Quintin,

Yes, I am 100% sure there is 0 filters, 0 Row Level Security and NO interaction from other visuals on the table visual.

Regards,

Satya

[SatyaSunitha1_]

Hi Quintin,

I also checked with my colleague in his system with his credentials, also faced the same issue.

Regards,

Satya

[Stachu]

Are you exporting from Dashboard or Report view?
I think in Dashboard the export by default is summarized rather than underlying data

[SatyaSunitha1_]

Hi,

From Report view.

Regards,

Satya

[Stachu]

hmm, according to this:
https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-export-data
there is size limit that can limit number of rows:

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.

Could that be the case?
Have you considered using Analyze in Excel instead of extract?

[SatyaSunitha1_]

Hi Stachu,

Regarding the mismatch between no. of records displayed and no. of records exported to excel from PBI Table Visual, done the necessary investigation and below are my observations -

1. For testing puprose, used the sample data below consists 5 rows and 22 columns of data of which 20 columns are Text data type and 2 columns (AmountUSDK and Quantity) are decimal number data type.
2. 1 record has been duplicated, which has the amount 0.80029$K.
3. After importing the data to PBI Desktop, used Table visual and dragged all the columns to values fields. For Quantity and AmountUSDK used Don't Summarize option, so the number of records to be 5 but I see only 4 records (1 record which is duplicate is eliminated which should not be)

Metric Type

Calendar Date

Country

Sales Order Type Code

Sales Order Type Description

Sales Channel Route Name

Business Area Code

Base Product Product Type Identifier

Big Deal Identifier

AmountUSDK

Quantity

GTM

Mapped MDCP Org ID

Mapped MDCP Org Name

Mapped Partial Sales Territory ID

Mapped Partial Sales Territory Name

Mapped Sales Territory ID

Mapped Sales Territory Name

Mapped ST Associated Reference ID

Mapped Top Sales Territory ID

Mapped Top Sales Territory Name

Mapped AMID L4 ID

Upfront

14/04/2016

Hong Kong

XYZ

Debit Order

Direct

ZX

YO

93477500

0.8002892

0

GLOBAL

53635356

Hang Seng Bank, Limited

154734

Test

Test1

336341

Tes3

Test4

Upfront

13/07/2017

Hong Kong

XYZ

Debit Order

Direct

ZX

YO

40584632

0.1967462

0

GLOBAL

154734

Test

Test1

336341

Tes3

Test4

Upfront

13/07/2017

Hong Kong

XYZ

Debit Order

Direct

ZX

YO

40584632

3.0917166

0

GLOBAL

154734

Test

Test1

336341

Tes3

Test4

Upfront

26/05/2017

Hong Kong

XYZ

Debit Order

Direct

ZX

YO

40506119

1.4461537

0

GLOBAL

154734

Test

Test1

336341

Tes3

Test4

Upfront

14/04/2016

Hong Kong

XYZ

Debit Order

Direct

ZX

YO

93477500

0.8002892

0

GLOBAL

53635356

Hang Seng Bank, Limited

154734

Test

Test1

336341

Tes3

Test4

Regards,

Satya

[SatyaSunitha1_]

Hi,

No, I issue is not yet resolved.

Have sent test data to investigate, awaitng for reply.

Regards,

Satya

[quentin_vigne]

@SatyaSunitha1_

Sorry but I have absolutely no idea where do this come from.

Quentin