Data being deleted when desktop report is published to Power BI service

[Sam_Fischer]

I have a .pbix report (which draws on SQL Server data sources), and I've published this report to a workspace for which I am an adminsitrator. When I publish this report to powerbi.com, the measures in the visual change, which I would not expect to happen. When I download the report as a pbix file and compare the original file to the file downloaded from the service, the only discernable structural difference between the two is that the original file has 2079 rows in the main fact table, but the file from the service only has 1000 rows (exactly 1000) in the same table.

Has anyone else ever seen this behavious of the Power BI service truncating rows and has any idea what's going on? I've investigated security groups and looked at whether each report has different filters applied but I haven't had any luck finding an explanation.

[GilbertQ]

Hi there

From the sounds of it, if you go into your source query, you have got a "SELECT TOP 1000" in there which is causing it to only show the top 100 rows. Just double check that this is the case.

And to confirm you are using Import Mode?

I personally have never had any data missing once uploaded and refreshed, when it has happened there was something in my code which was causing the change.

[Sam_Fischer · Accepted solution]

There aren't any SELECT TOP [x rows] in my queries, but I had been importing the data by adding the SQL database as a source, then navigating to the table I want. I've found that the issue disappears when I instead add a select * from [table] statement to the source Advanced Options, so I get straight to the desired table in one step.

I'm not sure why Power BI service would notice a difference, since I would think that conceptually the two approaches would accomplish the exact same thing; it could be a subtle bug in the service.

In any case, thanks for your help @GilbertQ

[v-jiascu-msft]

Hi @Sam_Fischer,

Glad you fixed it. This issue is weird. It's hard to reproduce.

Best Regards,
Dale

[GilbertQ]

That is indeed interesting and not sure why that would happen.

Glad you fixed your issue.