cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NiklasAlmen
Frequent Visitor

Only the first 1000 rows is loaded into the Data model

Hi!
I use a Azure SQL database connector to load (Import Mode) our DimDate table where we have all dates reprecented in the year 2010, 2021 and 2022.

When I load this table into the model I only get the first 1000 rows (Min date = 2020-01-01 and Max date = 2022-09-26)
In Power BI Desktop - When i sort the date column (desc) i get 2022-09-26. 

NiklasAlmen_0-1636020771616.png

When I look in Power Query and sort the date column (desc) i get as expected 2022-12-31 (or aquivalent 31/12/2022). 

NiklasAlmen_1-1636020939899.png

If I then Close and apply i get the following result in the data model,

NiklasAlmen_2-1636020983538.png

and if I then sort the date column (ascending) I get the "min" date 2020-04-06 which proves I only get the first 1000 rows,

NiklasAlmen_3-1636021041468.png

I have tried 4 different connectors that give me the same result. 
I have also tried the Table.Buffer() function that was suggested in a similar issue in 2018 but it doesn´t sort out this issue. 

I use the 2021 Oct version of Desktop,

NiklasAlmen_4-1636021139495.png

Thank you! 

4 REPLIES 4
Greg_Deckler
Super User
Super User

@NiklasAlmen Paste your code from Advanced Editor. Could be an issue with the View or Stored Procedure you are pulling from limiting it to 1000 rows.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Thank you for the reply @Greg_Deckler!
Here you have a snapshot of the M code, 

NiklasAlmen_0-1636035477114.png

If I do a select * from the view in the database I get the expected result and due to that I also get the expected result (all dates in the mentioned years) I had basically ruled out the option that it could have something to do with the backend.

@NiklasAlmen Well, 1000 is the default amount of rows pulled for preview purposes in Power Query Editor but that should go away and all rows should load once you load the data. You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Thanks @Greg_Deckler!
I have created a support ticket and talked to the support team yesterday, and they are looking in to it.

 

Thanks for your support! 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!