Each data refresh gives different data results

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