cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wxrhxl Frequent Visitor
Frequent Visitor

Appending two tables together alters the data in one of the original tables

I have two tables that I append together (table 1 and table 2).  When I append them together to form a new table, the data supplied by table 1 has duplicated values in some areas and doesn't pull over the original values in other places (the number of duplicated and missing data change with each refresh) .  Nevertheless, the original table 1 continues to have the correct data in it.  Meanwhile table 2 continues to remain unchanged.

 

I have the exact same setup in excel using power query and I do not have any trouble.  This issue only happens in power bi

6 REPLIES 6
Seward12533 New Contributor
New Contributor

Re: Appending two tables together alters the data in one of the original tables

I have never seen the queries in power bi and excel behave differently. Are you using power pivot in excel or just copy pasting the two data sets together. Are base queries and append queries the same in both?

My guess is that your rmeving duplicates. Are you doing that on the entire table (all columns selected before you remove duplicates or are you removing duplicates on a single column?
wxrhxl Frequent Visitor
Frequent Visitor

Re: Appending two tables together alters the data in one of the original tables

I am using power query in excel and then I load the table to the data model.  The queries are exactly the same in both.  In fact, I used the import function initially  to bring over the exact setup that I had in Excel's power query (later, I rebuilt it from scratch with the same result).  I don't remove duplicates at any point in time nor do I have any data transformations outside of the append.  Furthermore, when I pivot the contents of table 1, the data appears correct, but when I pivot the appended version of the table the contents are incorrect.

Seward12533 New Contributor
New Contributor

Re: Appending two tables together alters the data in one of the original tables

Very odd - just to confirm the pivoted values of the merged tables are incorrect in the power query environment? Or are they incorrect in when you display results in a visual I PowerBI?

Please share some details/examples or ideally a link to a workbook.
wxrhxl Frequent Visitor
Frequent Visitor

Re: Appending two tables together alters the data in one of the original tables

They are incorrect in both the power query environment as well as the displayed results in Power BI.  I will need to prep some examples.

wxrhxl Frequent Visitor
Frequent Visitor

Re: Appending two tables together alters the data in one of the original tables

I found this on the support site showing awareness for what appears to be my issue.

 

"Customers utilizing specific connectors without using native SQL queries, pulling SQL data that does not include a SQL primary key column in the final result, and doing Append or Merge operations may experience missing or incomplete data when refreshing reports in the Power BI service. The following is a list of connectors impacted: SQL Server, Oracle, PostgreSQL, MySQL, Teradata, Sybase, Informix, DB2 or Access.Next update @07/30/2018 12:00 PDT. "

 

Outside of fabricating a primary key (which I did and it didn't do anything), I don't see many other actionable steps. I believe my SQL is automatically a native query by virtue of the fact that it has code embedded in Power BI. I might be wrong about this, however. Thoughts?

Seward12533 New Contributor
New Contributor

Re: Appending two tables together alters the data in one of the original tables

Try adding a Transformation in power query immediately after importing the data that will intentionally break query folding. This will performance issues since your forcing POWERBI to do the work vs pushing it back on the server. Can’t test it but with a shot. Also try this forum on Technet https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery
It’s actively monitored by The PowerQuery team and is the best place to get answers and they may have a workaround or details posted in an existing thread.