Limit of 1000 value reached

[ChenChristyYu]

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!!!!

T

[rocky09 · Accepted solution]

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

[Vaishnavi_Mani]

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?

[alwweb]

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

[Vaishnavi_Mani]

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?

[alwweb]

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

[rob1974]

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.

[fushrl]

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!

[rob1974]

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 · Accepted solution]

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

[arsanious]

i have 3680 rows in query editor , however, i am not able to visualize all of them in table visualization it visualize only 937 elemets. there is no filter applied, i have tried all recommendations but still not able to visualize the full rows.

[Anonymous]

Nope. Its not 

[Nobuko_ao]

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

[rocky09]

@Nobuko_ao

Read the 2nd post.

[Nobuko_ao]

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

[RMDNA]

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

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.

[Nobuko_ao]

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

[RMDNA]

@Nobuko_ao,

Correct.

[Anonymous]

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

[alwweb]

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]

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 .

[alwweb · Accepted solution]

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