cancel
Showing results for 
Search instead for 
Did you mean: 

Only first 1000 rows loaded to model

I have no clue what is causing this and I cannot reliably reproduce it (and unfortunately cannot share my PBI file). However, sometimes Power BI will only load the first 1000 rows of a table to the model, like a Power Query preview, but the model itself actually will only contain the first 1000 rows. Can anyone reproduce? It seems to occur when there's some sort of merge function in the query, but I'm still clueless as to what is causing this. Refreshing the data model will not fix it.

 

Regards

Status: Needs Info
Comments
Frequent Visitor

Still not sure what is causing this, but Table.Buffer seems to be helping.

Moderator

Hi @LukeWalker,

 

I have tested in Power BI desktop July version with importing data from Azure SQL database, but not able to reproduce the issue. 

 

Did you get data from Azure SQL database via Import mode or DirectQuery? Do you specify any T-SQL query when you get data? 

 

After you retrieve data, is there any steps you applied in Query Editor? 

 

Best Regards,
Qiuyun Yu 

Established Member
Status changed to: Needs Info
 
Frequent Visitor

Hi @Vicky_Song,

The data is imported from a SQL server. I do not user T-SQL. I do apply steps.

let
    Source = Sql.Database("server", "database"),
    dbo_jobmatl_mst = Source{[Schema="dbo",Item="jobmatl_mst"]}[Data],
    #"Removed Other Columns" = Table.Buffer(Table.SelectColumns(dbo_jobmatl_mst,{"job", "suffix", "item", "matl_qty"})),
    #"Merged Queries1" = Table.NestedJoin(#"Removed Other Columns",{"job"},item_all,{"job"},"item_all",JoinKind.LeftOuter),
    #"Expanded item_all" = Table.ExpandTableColumn(#"Merged Queries1", "item_all", {"item", "p_m_t_code"}, {"item_all.item", "item_all.p_m_t_code"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded item_all", each ([item_all.p_m_t_code] = "M") and ([suffix] = 1)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"item_all.item", "jobmatl_parent"}, {"item", "jobmatl_child"}, {"matl_qty", "jobmatl_child_qty"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"job", "suffix", "item_all.p_m_t_code"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"jobmatl_parent", "jobmatl_child", "jobmatl_child_qty"})
in
    #"Reordered Columns"

Here is my code. For security reasons the database info is redacted. Without the Table.Buffer function, only the first 1000 are loaded. However, with it, the entire table is loaded. 

Frequent Visitor

Here is a static file that reproduces this bug:

https://tropiclean-my.sharepoint.com/:u:/p/luke_walker/EcWRe-vOw_pPhJAc2LS36loBRB5Zw4DOzXz829U1B2Sot...

 

The '1000' table only pulls the first 1000 rows. The BUFFER table uses the table.buffer function to pull all rows. However, it appears on refresh the 1000 table pulls all rows. However, in the live dataset (from the SQL server), the 1000 table still pulls only 1000 rows.

Moderator

Hi @LukeWalker,

 

This is a known issue. Currently there is no workaround, Engineering team is working to release new version of July Power BI Desktop. You can keep an eye the status of this issue on Support site: 

 

q8.PNG

 

Best Regards,
Qiuyun Yu 

Anonymous
Not applicable

 Hi @v-qiuyu-msft,

I'm experiencing the same issue. Am using the MySQL connector.

 

Table.Buffer does seem to help.

 

Your given URL does not seem to link to this specific ticket/issue, on the support site?

Cheers,

Amos

Regular Visitor

I think I'm experiencing the same issue - this is a new issue. I have the latest Desktop file as of today (2018-08-07) and can still reproduce the problem.

 

I have a PBIX file that has been happily in use for close to 3 years. I worked with this PBIX file without issue just a couple of weeks ago. But when I do a refresh now in Desktop, a table that does not have a primary key shows as having 55k rows, and only 1000 are actually loaded when I close and load from Power Query.

 

HOWEVER, when I go ahead and publish the file to the service and schedule a refresh, the data loads correctly.

Frequent Visitor

@Anonymous @JenM Make sure you are using the latest version of PBI (Version: 2.60.5169.4101 64-bit (July 2018)). I believe this was fixed in a recent update.

 

Regards

Visitor

I'm using version 2.60.5169.4101 64-bit (July 2018), but the issue is not fixed for me.

I'm going to aks our IT department to downgrade to the June version so I can continue my work.