cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Impactful Individual
Impactful Individual

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

View solution in original post

@VB  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

16 REPLIES 16
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.

Impactful Individual
Impactful Individual

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

View solution in original post

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

 

Impactful Individual
Impactful Individual

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

Regular Visitor

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 @VB . 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.

Regular Visitor

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 .

@VB  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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors