Forum Discussion
Community ChampionMore 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.
Community ChampionWhen a query runs, it will tell you what it is doing. I've seen it count to over 1M rows for large tables, but after grouping, filtering, summarizing, it may only load a few hundred. This is especially true for non-SQL sources or SQL sources where folding isn't working as Power Query is doing all of the work. So in your example, it has to process 10 rows to show you the 5 you filtered for.
As to your 100 rows loaded vs the 20 you think, we'd need to see data and the M code to see how your join is set up. Joins can increase, decrease, or leave row count unchanged. Depends on the join type and the data. For example, if you have the following tables with these records (pretend they are the customer number in the table):
Customer
1
2
Sales
1
1
2
2
if you join Sales to the Customer table, you'll get 4 rows in your customer table query.
And yes, the Mashup engine in the gateway is supposed to be the same as the engine in the Power BI Desktop file. That is why it is important to update them both in the same month. Usually the gateway release trails the desktop by 1-2 weeks.
Community ChampionHi Edhans,
Yes the query shows that it is running through 10 rows but it loads only 5 into the desktop, fine but it takes time consuming.
regarding the other table of 100 rows, I myself don't know how to do the joins with M code inside Power Query, i am not into coding so i have to simply rely on my Desktop to do the work but that after i get all the 100 rows already loaded in. This behaviour is time and size consuming.
the other dilemma in this whole process is when for example, in my table X i have 2 accounts that don't have any transactions today in Table Y and in power query I selected these 2 accounts to appear whenever new transactions are created. So in my desktop i don't see these to 2 accounts today in order to check box them in my Filter Page and i will have to verify every day if new transactions are created in order to be able to do so. As i said previously if i dont check box the account nembers that i need to see their Total sales then i will see the Total sales in the Table Y.
Not sure if Microsoft have a solution to this behavior.
- edhans
You don't need to know how to code to do joins/merges in Power Query. It is all in the UI. See this article for more. As for the other issues, I'm having trouble understanding what you are trying to accomplish with your description, so perhaps sharing your file would help.
- aj1973
Hi,
Can the Indexes in Query Editor solve the issue? meaning when I refresh in desktop the uploading query runs through only filtered rows instead of all the 100 rows in my table. so the refresh does not take longer!
- edhans
No. Adding index columns will slow it down, because you are adding index columns. In PQ, adding an index is just a column of numbers usually starting at 0 or 1 and increment. It useful for matching with a previous row, etc. It is not like adding an index to a relational database (SQL, Access, etc) that can speed up the queries.
Not sure what you are doing exactly, but no way 100 records should take more than an eye-blink to read in unless your data is on an online site that is really slow. For example, if you have a CSV file with 100 records you are reading in from a file that is stored in SharePoint and you are accessing it via SharePoint.Files(), it has to read in every single file name and metadata before it even opens your CSV file.
But without more specifics I am just guessing here. As I said, if you can post a sample file, or give a complete picture of what you are doing (the data source, where it is, how many rows, how many columns, etc) it would be helpful.