Only first 1000 rows loaded to model

[LukeWalker]

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

[LukeWalker]

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

[v-qiuyu-msft]

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 

[LukeWalker]

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. 

[LukeWalker]

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.

[v-qiuyu-msft]

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: 

Best Regards,
Qiuyun Yu 

[Anonymous]

 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

[JenM]

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.

[LukeWalker]

@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

[RuudPolly]

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.