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
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.
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.
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?