Using native SQL Queries against data sources can come with certain advantages. Chief among those advantages is the ability to push data transformation tasks to the data source level, rather than on Power Query itself.
Excel of course is just a flat data source without natural ability to be queried with the SQL. But with the aid of Open Database Connection Driver (ODBC), we can find a way around that.
In this first part of my article on the subject, I will describe the basic steps to achieve this feat.
Note that spaces are not allowed for Excel Named Ranges. (There are ways to programmatically create a dynamic range using Excel formulas you can lookup online. I will probably include this in the second part of the article).
Now the Excel file has been set up and ready to be used by ODBC connector on Power Query.
We can do a quick check by right-clicking on the Navigation Step in the applied steps area in Power Query to view the Native Query.
There are still other areas to touch as far as using Excel ODBC connection on Power Query is concerned. What are some of the limitations? How do you write out your own SQL Queries? Will Query folding always happen for various Power Query Transformation steps?
I will share more details on these in the next piece, while hoping that the Power BI Community also contribute on this subject.