Each data refresh gives different data results

[Anonymous]

Hello,

I created a .PBIX file with a model and it has been working correctly for 3 months. Data sources are SQL Server 2014 and some Excel worksheets.

After updating Power BI Desktop to the latest version, everytime I refresh the data it gives different results and they are always wrong.

I have checked all the queries and the relations between tables and there have been no changes. I have also carried out tests on the data sources with SQL Management Studio and everything is correct.

Everything would suggest that Power BI imports a different number of rows at each refresh (even when I press the refresh button every few seconds), but the table contains always the same (correct) number of rows. It seems that the error is an issue that involves only the Reports.

What could be the reason?

Thank you for any help.

[orangeatom]

Replying to thread to let others know that I am encountering the same issue (two different results based on the same source refresh. The model is a bit more complex and there is a lot going on with alot of source data). My version is: 2.57.5068.721 64-bit (April 2018). Current temporary solution for is to revert to previous working copy.

[ThijsL]

Sorting the tables which are loaded into your diagram set-up seems to work.
You can add this as a last step in the query of each table by clicking on the column with the most unique data and sort a->z.

Had to do this for each table separately both for the data tables and the labeling tables (labeling as in merging hierarchy levels with  key fields of the data table or just one like "manager" adding to each "projectkey").

[Ruchita]

Hi,

I am facing the same issue. After every refresh in Power BI service, the figures on the visuals change. Like value for different projects is showing different on every refresh but the total value is always the same. I tried to add sorting the tables but then refresh on the desktop itself seems to go on infinitely. Can you help?

[Petr_Strejc]

Hi,

I'm experiencing the same issue as people are describing here. I'm using Power Query "append" but "merge" seems to do the same based on Ina's post. Let me demonstrate the problematic behavior using test data.

First, we need a query that returns the same data but in random order. I will use a MS SQL stored procedure but I think any DB query returning data in a different order every time we rerun it would do.

create procedure create_test_data(@max int = 0) as
BEGIN
 WITH
   Pass0 as (select 1 as C union all select 1), --2 rows
   Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
   Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
   Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
   Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
   Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4294967296 rows
   big_table as (select row_number() over(order by C) as n from Pass5)
 select n, Abs(Checksum(NewId())) % 10 As RanInt
 from big_table
 where n <= @max
 order by RanInt
END

Column RanInt is used to randomize the order. We will be using only column n that contains integers from 1 to @Max and therefore sum(n) depends only on @Max.

Second, I create two tables in Power BI Desktop. Both have the same definition:

Third, I create a new table in the Query Editor window using "Append Queries as New":

This generates Power query: Table.Combine({Query1, Query2})

As a last step, I create simple reports based on each of the two input tables Query1 and Query2 and table Append1 created in the previous step. I use count(n) and sum(n) as my metrics.

When refreshing the report (i.e. the underlying data), Query1 and Query2 returns correct results, Append1 returns correct number of records but incorrect sum.

Currently, I'm using version: 2.51.4885.701 64-bit (October, 2017) but the July version had the same issue. The older version allowed me to copy & paste data (from the left Data menu) so I could see what Ina is describing in another thread - records have been loaded 0-2 times. This makes me guess that Power BI is reading the data while it's being refreshed which causes the inconsistency when the data are loaded in different order than previously. Based on network traffic Power BI seems to execute the stored procedure 3 times. BTW why is it no longer possible to copy data from tables in the Data tab? That's a significant step backwards IMO.

You might need to increase the number of records generated by the stored procedure. In my environment, 1000 rows per query was ok but 10k rows caused incorrect results. Just hit “refresh” in the report window and you will see.

When using production data, sometimes even sum of Query1 produced incorrect results when all inputs were refreshed at once (refreshing just the single input was ok). However, I couldn’t simulate this using the test data. DAX union works well meaning there is a workaround within Power BI. However, even if this behavior is "feature" and not a bug, we need to understand what’s the cause and what else should we avoid but merging/appending data in Power Query to stay out of troubles.

Thanks,

Petr

[InaNortje]

Hi @Petr_Strejc

Excellent description of the issue!  Thank you very much for providing steps to recreate the issue. 

I sent a request to Chis Webb to have a look at this issue.  Of all the people I know, he is most familiar with Power Query and M, and he has good contacts at Microsoft who may take up the issue. 

Another person that may be able to help is Koen Verbeeck.  

Regards

Ina

[hbarber]

Hi there,

Has there been any update on this?

I have just created a report on PowerBI but each time I've refreshed it today I get a different total.

The underlying data is pulling from SQL tables which are updated overnight so that data is not changing inbetween refreshes.

Initially it took 7 refreshes to get the figure I was expecting but then I just kept refreshing to see how many times it would take to get that figure again and it took 18 times, then 7 again.

Any help would be greatly appreciated!

Thanks

Harriet

[Matty]

Hi,

Any news on this?

I don't know how prevalent the issue is, but it feels like a fairly fundamental problem that needs addressing.

Cheers,

Matty 

[robstewart89]

I've had this same issue.

Sometimes I even get an error message saying there was a duplicate value in one of my tables. I then refresh again and it pulls through, but as before, the data is giving different numbers

Really feels like it's doing something different everytime I click refresh

[kdecolve]

Hello 

I figured out that there was a problem with the merge query. 

I solved this by merging the tables in SQL Server. 

[robstewart89]

Hi @kdecolve do you have any more detail on how you found the problem? I'd rather stick to merging my queries in Query Editor rather than in SQL Server.

[InaNortje]

Hi @robstewart89

Do you have an explicit “sort by” on each of your source queries?  If not, just update them all with an explicit sort.  That did it for me, so I was able to still use the Power BI Merge function.  Hope this may help.

If that’s not an issue in your case, let me know and I will give you some pointers on how to find out the cause. 

Regards,

ina

[kdecolve]

Hello

I'm facing the same problem. 

Context: 5 sql server views merged into 1 table in PowerBI. Each time I refresh, I get other results.

Very strange!

Solved by merging the results in SQL Server. But: this needs to be solved to avoid people loosing faith in the service.

[v-micsh-msft]

Hi zikas,

Could you please share about your current Power BI desktop version? Check it under File->Help->About. Mine is 2.38.4491.282.

I created some test data under SQL server 2014 database and the refresh is ok at my side. If the Power BI desktop version you currently used is not the latest, then please download the latest version and see if issue insists.

If Version is OK, then enable the trace log under:

File->Options and Settings->Options->GLOBAL->Diagnostics, check the Enable Tracing check box, then refresh the data again. After that, open the trace log file location, upload the log file into OneDrive and post us the shared link here, we will help to take a look.

See my version.

By the way, could you please explain a bit about the "involves only the Reports" here, do you mean refreshing data under Power BI desktop will cause the report Visuals to behave differently?

Regards

[InaNortje]

Hi @v-micsh-msft

I seem to have a very similar issue as posted here.

SQL 2016 and Excel data sources.  Every time I refresh, I get a different result set appearing on my reports.  Some rows are missing, others seem to be duplicated.  Each refresh yields a slightly different result set, and each time it is incorrect.  

I created the model and handed over to a business user late November 2016 year.  About 2 weeks ago the user reported that some historical results kept changing.  I am not sure at which point exactly this issue started, but I am now troubleshooting the issue on Power BI Desktop version 2.42.4611.701 64-bit (January 2017), and getting this strange behaviour consistently.  The user reporting the issue is running the November release, so it seems to be across different versions of PBI Desktop.

Can I go ahead and add to this post, or should I open another thread?  I have the trace logs ready.

Regards

Ina

[RDDWH]

I am also having this exact issue.  It seems to appear when I'm using an SQL Stored Procedure and passing variables to it (the variables are taken from another table to allow the user to change them before execution).  I also put some logging in the Stored Procedure and the 1st time the query is refreshed (after changing the variables) the Stored Procedure gets executed anywhere from 4 to 11 times and returns duplicates and missing data!  Any subsequent refresh only executes the proc once.  I've also captured the raw results before they are sent to Excel.  All return the same results set with the same counts so it's defintely something Power Query/Excel is doing.

[lizrowden]

I am also having issue. Has anyone found the cause or a solution? I am on the latest version of the Power Bi desktop. The data in the query editor is fine and the same number of rows are returned, but everytime I click the Refresh button on the report I get a different result even though the underlying data and report has not changed.

[Qazafi]

This is indeed really annoying, I am facing same problem since last three day every single time I refresh it gives me different results L I am using Power Bi latest release September. Also using SQl Server 2012, I tried using SQL Server 2016 No change in behavior. Can someone please respond

[InaNortje]

Hi @Qazafi

Add an "order by" clause to the source query - that solved the problem for me.  The issue only starts once the data volumes get really high.  Power BI seems to run the query twice, getting the first x rows in query 1, and then total-x rows in query 2.  It assumes that the order is the same, and then you get some duplicates and some missing records.

Let me know if that works for you too.

Thanks

Ina

[Ruchita]

I am also facing the same problem. After every refresh from Power BI service, the data in the reports changes. My data is on BigQuery and I have created views from where I am pulling the data into PowerBI. I tried to sort the tables in power BI using sort but the refresh in power BI desktop is going on infinitely. So I tried by adding Order by clause in the views in BigQuery. But it didn't help. Still the data changes on every refresh.

[InaNortje]

Hi @lizrowden, @Qazafi

Below is a link describing the full details of the issue that I had, and how I finally resolved it. 

The conditions under which it occurred are:

 - High volume source data 

 - Subsequent merge

(note: the query doesn't neccessarily have to return high volume data.  It's the source table size that seems to determine this)

https://community.powerbi.com/t5/Issues/Power-Query-Merge-yields-inconsistent-results/idi-p/119485

The easiest fix was to add an explicit "order by" clause to my source query.

Hope this helps

Ina