Edit: There is currently no interest at Microsoft to change the current behaviour: http://community.powerbi.com/t5/Issues/Bug-Query-folding-not-working-with-non-SQL-datasource/idi-p/225100#M11611 .
I found that this workaround works also pretty well (faster in most cases), as long as your filter-tables are not too long: http://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503
Are your SQL queries that filter with a non-SQL-table slow in PowerBI and PowerQuery in Excel? Then this might be of interest for you:
Nearly 2 years ago when I published my first blogpost about the bug that prevents query folding on SQL-sources when filtered by non-SQL-sources, PowerBI was still so new that I even didn’t mention it in my blogpost. I had enormous problems with some clients work with performance and spent 2-digit hours on figuring out the reason with some good help from the TechNet-Forum.
Today I was notified that “this bug was deemed as not high-pri enough to warrant a fix”. So it’s well alive and sucking: In Excel as well as in PowerBI. Considering how many people read my article, I found it worth to mention again and provide an improved solution. It is a function that’s easy to implement and takes the name of the tables and its key-columns as parameters:
Code to fix Query-Folding bug in PowerBi and PowerQuery
Code to fix Query Folding Bug in PowerBI and PowerQuery
And the code to copy:
FilterSQLTable.txt
Hope this prevents you from wasting precious time.
