Data in PowerQuery does not Match SQL Database

ksan · ‎07-28-2017

Good Morning.

Let me start by saying a newbie, so I may not be using the tool as intended.

I'm trying to match data from a list I imported to PowerQuery (call it Table 1) and ensure that those records do not already exist in a table in our database. I've connected to our SQL server via PowerQuery by specifying the table and the columns of interest in a query under the advanced options (table 2).

Next, I went to merge queries and joined table 1 with table 2 (left join as I wanted to know which matched and which didn't) as I've done before without issues. This time, my counts seemed a bit odd, so I validated by running the same query within SQL server. The count of records for Table 2 in SQL server match the count of records I have in PowerQuery... but when I match table 1 to table 2 in SQL server, I match on 20 additional records.

So... I went back to table 2 in PowerQuery and filtered on text contains and plugged in a random email address from the 20 missing and, sure enough, it's does not exisit in the table.

My question is, why doesn't the data in table 2 mirror what I have in SQL server if it's drawing from the same source and using the same query? 20 records isn't a huge deal, but it just makes me question if this has happened, at a larger scale, on any similar projects.

Any thoughts?

Thanks in advance,

Kari

Eric_Zhang · ‎07-31-2017

@ksan wrote:

Good Morning.

Let me start by saying a newbie, so I may not be using the tool as intended.

I'm trying to match data from a list I imported to PowerQuery (call it Table 1) and ensure that those records do not already exist in a table in our database. I've connected to our SQL server via PowerQuery by specifying the table and the columns of interest in a query under the advanced options (table 2).

Next, I went to merge queries and joined table 1 with table 2 (left join as I wanted to know which matched and which didn't) as I've done before without issues. This time, my counts seemed a bit odd, so I validated by running the same query within SQL server. The count of records for Table 2 in SQL server match the count of records I have in PowerQuery... but when I match table 1 to table 2 in SQL server, I match on 20 additional records.

So... I went back to table 2 in PowerQuery and filtered on text contains and plugged in a random email address from the 20 missing and, sure enough, it's does not exisit in the table.

My question is, why doesn't the data in table 2 mirror what I have in SQL server if it's drawing from the same source and using the same query? 20 records isn't a huge deal, but it just makes me question if this has happened, at a larger scale, on any similar projects.

Any thoughts?

Thanks in advance,

Kari

@ksan

This sounds really odd, as I'm not able to reproduce this behavior, could you share me any problematic data that could help to reproduce this issue? Since you're using SQL Server, so maybe you can export those two tables schema and data?

Anonymous · ‎10-03-2019

I am having the same issue!

My Query works in excel PowerQuery, but running the same thing in BI has at least one record that is off.

No matter what I try, power BI is displaying the "call date" for a particular record as 7/26/2019. Everywhere else the date is displaying as it appears in the database I am referencing: 7/29/2019.