Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChenChristyYu
Helper II
Helper II

Limit of 1000 value reached

Hi Experts,

 

I am new to PowerBI.

I am pulling data from servers in my corp, and I notice this error on my edit query page. Does this error message mean that I can only load and show a limited amount of data on my dashboard? I do need all the data pull in, what should I do?

 

Thanks!!!!

Tdata load bug.PNG

2 ACCEPTED SOLUTIONS
rocky09
Solution Sage
Solution Sage

@ChenChristyYuNo, it is just limitation. But, you will get all data into your Data Model. Cheers.

View solution in original post

@Anonymous  The drop-down list is limited to 1000 rows.  If the "N/A" is in a column wiht mostly numbers, you can click to reserve sort on that column, and then you can find the NA in the filter option to find all of the rows with it.  Unfortunately, if it is in the middle, you can't find the rows that way.  

 

If you are patient, all of the rows are in the result set.  You just have to scroll and scroll and scroll.  I used the Sales.SalesOrderHeader table from the AdventureWorks database and imported a table with 31k rows.  I gave up after scrolling to row 10,206, but I'm sure it is all there.  The Power Query Editor is for importing and transforming. Not really for analyzing.  There are better tools for that.   Scrolling beyond 1000 rows works for both import and direct query methods, so I'm not sure why you are only seeing 1000 rows.  

View solution in original post

19 REPLIES 19
Vaishnavi_Mani
Regular Visitor

Hi,

 

I am trying to filter out records by Department in Query editor and I have only 16 unique depts in my table. But PBI does not show all of them and gives the below msg. Pls can someone guide me why it does not display all 16 depts?

 

@Vaishnavi_Mani  

 

I totally understand your frustration.  Are you seeing the load more at the bottom of the screen or the 1000 value reached message?  In the filter, I have only had problems when there were more than 1000 disctinct values in the given column.

 

If you need to permanently filter, rather than doing it to test something and then deleting the applied step, are you using a source that will allow you to filter earlier in the import process?  Like SQL with a WHERE clause?

 

I was just trying to test this for you and it seems like my current version is returning more than 1000 values even though the message is showing I hit the limit.  I haven't compared all 100 values, but the first 20 and last 20 match my results from SQL (my source).

 

If you can provide a screen shot and more info, maybe we can help more.

Hi, Thanks for your suggestion. The 'Load more' worked out. 

 

I tried pasting the snapshot, a PNG file in my prev post, but it was not getting loaded. Is there any reason for this pls?

@Vaishnavi_Mani  I'm glad the "load more" worked for you.  Originally, it only pulls in distinct values from the first 1000 rows.  On larger tables, I've had to hit "Load more" more than once, but eventually I hit the 1000 loaded message.

 

I can't help much on the snapshot upload problem.  Did you try using the picture of the camera to upload images?  If that fails, try a jpg instead of a png.

The 1000 limit in the query editor is an issue for loads of data. I want to be able to get this a lot bigger (default a 1000 is fine).

 

My data contains loads of different objects, each with his own property set. When i expand the property set, i only see the properties of the first 1000 rows, which isn't by far the entire property set.

 

I can solve this, by manually editing in advanced editor the property set. But this is a tedious experience and takes a lot of time, being able to extend the 1000 row limit if it's needed would help a lot. Not only for my issue, but for the other issues in this thread as  well.

Hi Expert, May I understand how can I solve it by editing in advanced editor the property set? Can you please help share what to input under advanced editor? thank you so much in advance!

I'm using an Odata feed with a property which defines each type of data (within that type the datafields are the same). I can filter the types 1 by 1, check the advanced editor, copy paste from there whatever I needed and do this for all types. And then combining this to 1 filter.

But I've written a script which just pulls all data and then formats all columns in a way powerBI expects them. So now it's running the script and copy paste the results in the advanced editor instead of the "expand" lines that powerBI provides.
Still a **bleep**ty solution, rather have powerBI to not have a rigid 1000 line limit.



rocky09
Solution Sage
Solution Sage

@ChenChristyYuNo, it is just limitation. But, you will get all data into your Data Model. Cheers.

Nope. Its not 

Hi, experts.

 

I have same question and clicking <view solution in original post> but nothing had happen.

Please advise where can I find the solution.

 

Many thanks in advance,

 

best

 

Nobuko_ao

 

@Nobuko_ao

Read the 2nd post.

Dear rocky09,

 

What do you mean by 

>Read the 2nd post.

 

Where is 2nd post?

Sorry but I cannot find the solution of  this issue.

 

Thanks

 

Nobuo_ao

 

 

An accepted solution is always sticked immediately after the original post:

 

a.PNG

 

To answer the question again: no, the 1000 value limit in the Query Editor dropdown menus is only a limitation on how many values are loaded into the dropdown. It is not a limit on actual data importing.

Dear RMDNA,

 

Thanks, so your answer measn that "limit of 1000 is just  "how many values are loaded into the dropdown",

and my data (3,200 raw) is succesfully imported. 

Am I right?

 

best

 

Nobuko_ao

@Nobuko_ao,

 

Correct.

Anonymous
Not applicable

Hi ,

 

Continuing this thread , i am query editor and i need to remove "n/a" which are somewhere in row no 1500 but query editor displays only 1000 rows..would like to know if there is any other option to remove such blanks feilds as it gives Data fomat error during saving .

 

And how do we resolve errors reported in query editor.

 

Thanks for your time .

 

Cheer's

VB

Hi @Anonymous . Do you want to remove the entire where where n/a is in one particular column, or do you want to replace it with something different?

 

To replace it with something different, go to the Transform tab and use the Replace Values option in the Any Column group. 

 

If you want to delete those rows, you can play with the M code behind the scenes, or you can change the n/a to ZZZZZZZZ (with as many z's as you need to make it appear first in a reverse sort). Once you click the ZA downarrow on the Home tab in the Sort area, you will be able to cliekc the column drop-down arrow and uncheck the box next to the ZZZZZZZ value, removing those rows.

 

If it is a date or numeric field and you want to keep the rows, but get rid of the error, you can either change the values as I described above - OR - you can change the data type to text for that column. Once it is text though, you will not be able to do math or date functions on that column.

 

Some companies use 12/31/9999 which is a valid date for missing or bad date values.  It gets rid of the errors caused by n/a but keeps the ability to use the date as a date later in the reporting process.

 

I hope this helps.

Anonymous
Not applicable

Hi @alwweb

Thanks for your detail update below will definitly try your tips.

 

Basically what i am after and i am sure i might be missing some thing from my side.

Query Editor lists only 1000 rows for say 5 columns and all rows have required data.

But on roew no 1002 and 10008 has "n/a" which we can't see in GUI .

 

I think there might be query mode if there are 10K rows and you just run particular find query to get results.

 

Thanks again for reaching out i will try your suggestions below .

@Anonymous  The drop-down list is limited to 1000 rows.  If the "N/A" is in a column wiht mostly numbers, you can click to reserve sort on that column, and then you can find the NA in the filter option to find all of the rows with it.  Unfortunately, if it is in the middle, you can't find the rows that way.  

 

If you are patient, all of the rows are in the result set.  You just have to scroll and scroll and scroll.  I used the Sales.SalesOrderHeader table from the AdventureWorks database and imported a table with 31k rows.  I gave up after scrolling to row 10,206, but I'm sure it is all there.  The Power Query Editor is for importing and transforming. Not really for analyzing.  There are better tools for that.   Scrolling beyond 1000 rows works for both import and direct query methods, so I'm not sure why you are only seeing 1000 rows.  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.