Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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
Hi Quentin,
I see 22,931 records after exporting the data from PBI service to excel.
Regards,
Satya
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
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)
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
If you export these data in a csv file and open it in notepad, how many records do you have ?
Hi Quentin,
Still same records 22,931
Regards,
Satya
From where (in PowerBI) are you exporting the data ?
Hi Quintin,
By clicking on ... elipses option on table visual (right side top corner).
Regards,
Satya
Are you sure there is 0 filters, 0 Row Level Security and NO interaction from other visuals on the table visual ?
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
Hi Quintin,
I also checked with my colleague in his system with his credentials, also faced the same issue.
Regards,
Satya
Are you exporting from Dashboard or Report view?
I think in Dashboard the export by default is summarized rather than underlying data
Hi,
From Report view.
Regards,
Satya
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?
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
Hi,
No, I issue is not yet resolved.
Have sent test data to investigate, awaitng for reply.
Regards,
Satya
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |