Extra Rows in Published Report - data corruption?

[Martin-Edan]

I have a report that pulls data from a SQL Server view via DirectQuery. In Power BI Desktop, the CertificateTitle column shows correct values (84 distinct values) and matches the result of a SQL Server query (SELECT DISTINCT CertificateTitle FROM views.CERTIFICATION_ENROLLMENTS).

However, when the report is published to Power BI Service, the column contains numerous additional rows. The extra values do not even exist in the SQL Server view. The count of distinct values explodes in the published report, though no errors or empty values are displayed in Power BI Desktop. I haven't a clue where the extra values are coming from. 

Here is an example of the query I use in Power BI Desktop:

let
Source = Sql.Database("xc-s-zw00462.xc.company.com", "BI"),
views_CERTIFICATION_ENROLLMENTS = Source{[Schema="views",Item="CERTIFICATION_ENROLLMENTS"]}[Data]
in
views_CERTIFICATION_ENROLLMENTS

I then remove duplicates. I am using this list of titles in multiple slicers on one page for the user to define training groups. But the list of certificates in the slicer, once published to the service does not match that of the desktop or SQL server view (i.e., many extra values of unknown origin). 

I've ensured that:

• The data source is the same in both the desktop and service.
• Data refresh is configured correctly.
• Power BI Desktop is up to date.
• Cleared cache in desktop and republished. 

I am unsure how to troubleshoot this discrepancy. Why would the Power BI Service dataset contain extra rows that don't exist in the SQL Server view? How can I resolve this issue?

[Martin-Edan · Accepted solution]

Hello, 
I had to abandon the SQL server connection and rely on manually inputting the data into PBI. The data won't change often so, given the time and resources available to solve this problem, I opted for a manual approach. 

[Anonymous]

Hi, @Martin-Edan 

You can mark your response as a solution so that others in the community can quickly find an answer if they have a similar problem. Thank you again for your cooperation!

Best Regards

Jianpeng Li

[Anonymous]

Thank you lbendlin 

Hi, @Martin-Edan 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What's the collation in your SQL Server table column?  Remember that Power Query is always case sensitive while SQL may or may not be (depending on your setting) and Power BI is not.