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

5 REPLIES 5
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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! 

@NiklasAlmen Did you ever solve this issue?  I am now encountering the same problem.

 

Thanks!

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.