Append table lost rows

[dgbraqe]

Hi, I'm having a problem loading two queries from different bases and merging them using the "Append as new" option. The resulting table is joining records incorrectly, following examples:

Base 1 - 33866 rows. Colunm ChavePedido (33866 distinct values)
Base 2 - 76326 rows. Colunm ChavePedido (76326 distinct values)
Base Append - 110192 rows. Colunm ChavePedido (110107 distinct values)

The result in the Append base should be 110192 distinct lines, mirroring base 1 and base 2. How to make Append correct?

[mgiusto]

It's now 2022 and I have same issue as laid out here in this thread. If I combine two tables in Power Query it will not actually have the contents of both tables, but if I do the DAX combine it will. The problem with that though is I need the append to work in Power Query because I need to do some other work with that appended table, when it is DAX it is not available in Power Query.

Does anyone have other ideas to get this to work in Power Query? I have the latest December 2021 update of Power BI and this issue still exists.

[edhans]

You should start a new thread, and give some info. the issue that was fixed in 2018 was very specific to certian tables in SQL Server.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[v-yuezhe-msft]

@dgbraqe,

Create a new append  table using DAX below and you should get correct distinct values.

Table = UNION('BASE 1','BASE 2')

Regards,
Lydia

[dgbraqe]

@edhans, perfect your observation.

When we use the DAX function, the problem does not occur.
This shows even more that we have a problem with the M (Power Query) language, I understand that the use of "Table.combine" (M language) or "UNION" (DAX language) should have the same result.

I opened a ticket with Microsoft, I will inform you as soon as I have a definitive answer.

@v-yuezhe-msft, please, leave the topic open until we understand the reason for the difference between the M and DAX language.

[edhans]

Thanks @dgbraqe. Please keep us posted on this.

I am wondering now if my issue here is a result of APPEND weirdness in M Language, as the resultant table is from an APPEND in M. Except in my case, it works in PBI Desktop, but not in the service. 

[Ehren]

@dgbraqe, I've tried reproducing this locally (using SQL data refreshed via a Gateway) and am unfortunately not seeing any duplication. Would it be possible for you to try the following?

• Try duplicating the query that does the Table.Combine operation, and then buffering the result (i.e. add a step at the end that does = Table.Buffer(previousStep)). Does this version of the query exhibit the same behavior?
• Try duplicating the query that does the Table.Combine operation, and buffering each individual table being combined (e.g. = Table.Combine({Table.Buffer(Query1), Table.Buffer(Query2)})). Does this version of the query exhibit the same behavior?
• Try duplicating the query that does the Table.Combine operation and doing a group by and count of the unique field you're seeing duplicated, then filter for Count > 0 (which should return an empty table when run locally in PBIDesktop). When refreshed via the gateway, does this query produce any results?

Also: what version of SQL Server are you pulling from?

Thanks for your help.

Ehren

[dgbraqe]

Hello @Ehren

I am using the SQL versions below:
Base 1 - Microsoft SQL Server 2014 (SP2)
Base 2 - Microsoft SQL Server 2016 (SP1)

About the tests you requested, the Microsoft team had already done some testing using the "Buffer". Yesterday I had another meeting with the product team, they did some tests and collected the "trace" of the data load. I was told that you are prioritizing the subject.
I warn you as soon as they return.

[Ehren]

Hi @dgbraqe. Yes, I was the one on the call yesterday. 🙂

We'd like to do a follow-up call today, if possible. There are some additional tests we'd like to run to further pinpoint the source of the issue. Please ping Raghu to see if he can set something up.

Ehren

[Ehren]

Hi @dgbraqe. Thanks so much for your patience today as we ran various tests on your computer. It was very helpful and provided a number of clues that should allow us to narrow down the issue further (still a work in progress). We'll keep you posted and let you know if there's anything else we need from your side.

Please know that we're treating this with a high priority and are doing our best to find the root cause and fix it.

Ehren

[Ehren]

Hi @dgbraqe. Thanks to the live debugging we were able to perform on your machine, we've identified the source of the issue.

We're working on the fix, which is currently targeted for our July release.

In the meantime, you should be able to work around the issue by ensuring that your native SQL queries sort the data using stable, unique keys. Based on examining your sample pbix, I'm guessing this would either be ChavePedido or PedidoEmissao. The goal would be that the first 4,096 rows of each table would always be the same, even when new rows are added.

Here's an example of how to modify your Source steps to add the ordering.

Current:

= Sql.Database("52.22.92.121", "sapiens", [Query="SELECT *#(lf)FROM USU_VBI005_PEDIDOS"])

New (assuming ChavePedido is the unique key, as described above):

= Sql.Database("52.22.92.121", "sapiens", [Query="SELECT *#(lf)FROM USU_VBI005_PEDIDOS ORDER BY [ChavePedido] ASC"])

If performing this workaround, be sure to do it for both Base1 and Base2.

Please let me know if you have any additional questions.

Ehren

[dgbraqe]

Hello @Ehren!

I ran the tests using sorting in the SQL command and it worked correctly.

Just one more question, I have some queries that I do not use the SQL command, I directly use the view / table in the database. In this case, how can I keep the data sorted?

I am happy to have helped you, and thank you for your commitment and dedication to solving this problem. Please let us know when the correction is available.

[Anonymous]

Hi, 

Could you please tell what you have done to have this worked? Did you do the sort in SQL or in PowerBi?

[edhans]

@Anonymous This was fixed almost two years ago and it was a pretty big undertaking on the back end to rework how the Power BI service worked to avoid the issue. The Desktop app was fixed at that time as well.

Perhaps you could start a new thread on exactly what issue you are facing, because this one isn't the same thing. I was one of those that reported on the issue and I have a report in my tenant that was designed to find this issue and it hasn't triggered an alert in over 18 months. I should just delete it honestly, but haven't done it yet.

[Anonymous]

Ok, many thanks for your answer. Luckily I gave the project away. So it is in fact not my problem anymore.

However, Iam still looking for en explanation for why is this happening. If that is a bug or so, then it is a dangerous one!

[Ehren]

Hi @dgbraqe,

When not using a native SQL query, you'll need to ensure the SQL query that we generate behind the scenes includes an ORDER BY clause. This can be accomplished in a few ways.

• You can ensure that the source table you're pulling from has primary keys defined, and then include all the primary keys in your queries (ensuring they are not removed downstream, as this can actually affect the upstream SQL we send to the server). In this case, we will automatically include an ORDER BY in the SQL we send to the server.
• If this isn't possible (for example, if you're doing a Group By, or pulling from a view which can't have primary keys, etc.), it's a bit trickier. Our recommendation is that just before you do the Table.Combine, you:
  • Sort the data by a set of unique, stable keys.
  • Buffer the result of this using Table.Buffer.
  • Verify that the SQL query we generate includes the desired ORDER BY clause.

The goal is to have the data ordered consistently when it's retrieved from the SQL Server, such that the first 4,096 rows are always the same. If you need any help applying these workarounds, please let me know.

Ehren

[Ehren · Accepted solution]

@dgbraqe, This issue should be now be fixed. Please download the Power BI Desktop July release and let us know if things are now working as expected.

Ehren

[Anonymous]

The Issue is not fixed, and it is available in 2020 januari edition

[edhans]

---

@v-yuezhe-msft wrote:

@dgbraqe,

Create a new append  table using DAX below and you should get correct distinct values.

Table = UNION('BASE 1','BASE 2')

Regards,
Lydia

---

Isn't that a workaround? Why isn't it working correctly, or at least, why aren't we understanding it correctly, in the M code?

[v-yuezhe-msft]

@dgbraqe,

Do you have duplicated values in ChavePedido column of Base 1 and Base 2 tables? For example, value A exists both in Base 1 and Base 2.

Based on your third screenshot, there are 110192 rows in the append table, which is correct. But for the  Colunm ChavePedido, there are 110107 distinct values.

Regards,
Lydia

[dgbraqe]

No, I do not have duplicate ChavePedido values. This field is unique in each database, to ensure that after joining the bases do not match, I use a "source" numbering to do the composition of this key. Still to make sure they do not duplicate, I also exported the data from each database to Excel and checked duplicity.