More rows in the final table than in the query editor

[Anonymous]

Hi everyone,

I have a problem I cannot figure out - no clue how this is even possible. I am working on an update for an old report, and have added a new page for which I have spend a substantial amount of time, so I hope I don't have to do everything back from the start. There is a discrepancy in total rows between the query editor and the loaded table in the original report.

The data is loaded and transformed from Microsoft Dynamics CRM, it is basically a list of all customers. The query editor in its very final step has 196 total rows and the loaded table has 458 rows in the original file.

Something seems to have been lost along the way in the new version. I cannot find the missing rows, so the loaded table has 196, same as in the query editor, leading to a lower overall number of customers - they are all distinct entries, there are no duplicates, there are no filters or relationships that might be interfering, I double checked everything and brought the new version back to the initial versions settings to make sure everything is equal. 

How is it possible that a table has more rows than the last step in its query editor is showing? How could they have been added to the final table? A merge or append function with another table would be documented in the query editor. I couldn't find anything related.

Thanks in advance,

Aki

Query editor:

Same table, in the "Data" tab after loading:

[Anonymous]

Thank you for your answers.

The data source is an OData Feed, from Microsoft Dynamics CRM. I have tried everything, there are no calculated columns and it seems that the .pbix version I downloaded from our PBI services had these additional rows saved somehow. Once I refresh the overview in PBI Desktop these 200+ rows are gone and I am left with the 196. What could be causing this? If it is deployed in PBI services the automatic refresh seems to work, only when I refresh manually in PBI Desktop those rows seem to dissappear. 

[Anonymous]

Hi @Anonymous , 

What is your data source? What have you done before the error?

I have found a similar post, please refer to it to see if it helps you.

More rows than filtered in Power query 

I think the problem is the ODBC driver. I just connected to my SQL server via ODBC instead of directly and it does not support query folding in Power Query. So what that means is Power Query is importing your ENTIRE TABLE contents for each table you connect to, and then it works with everything in RAM. It is very inefficient, and little better than working with CSV files. In fact, it is probably about the same, except your tables are coming in structured and the ODBC driver is hopefully passing along data types and other relevant metadata.

But Power Query isn't generating nice neat SQL statements and sending it back for processing. That is assuming it is a relational database to begin with. It may not be, in which case no folding will happen regardless of connection type.

You can validate this though. Pull in 1 table. Do 1 simple filter. Right-click on that step. Does it say "View Native Query?" If that is grayed out, you aren't getting the advantage of a relational database connection. If it is, my whole theory on your speed issue is shot - other than I don't know what your ODBC driver is doing exactly and it could be doing a lot of interpretation and processing slowing things down.

I had an issue a few years ago I had to connect to an old SQL server (2000 I believe) that didn't support features required by Power Query so I had to pull in entire tables for Power Query to work with. It was on site, so processing about 4M records across about 10 tables and doing joins and what-not took 20-25min start to finish on a PC with 16GB of RAM to return 7,000 relevant records. Your record count is smaller, but I've no clue how many columns you have or the column content. Plus you said you were working remotely, so the transmission speeds wouldn't match what I was getting via ethernet.

If it is a relational database on the other end, and you cannot do a direct connection vs bypassing the ODBC driver, you could create a view on the server. You can see here what data sources Power BI supports natively (no need for ODBC). Sources like Amazon Redshift, IBM DB2, Oracle, SQL, SAP HANA, PostgreSQL, Vertica and many others support query folding. A view would let you apply filters and remove unnecessary columns, radically reducing data transmission times and local processing times.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

This might be a long-shot but are there any calculated columns in the CRM table that are performing cross joins?