Pre-Filtering Data before Importing from xlsx or txt file - no use of #"Filtered Rows"

[patator35]

Hello

I have set up a table score card file for salesmen. At the moment, I retrieve all data from the datasource and then I use #"Filtered Rows" to only select the relevant sales area for the salesman using the file. It is too slow. The datasource (xlsx or txt) is located in a shared directory. The access can be slow using a vpn connection.

The sql native mode is greyed out in the PQ screen. It is probably due to the fact that I do not use a sql server to store the datasource...just a simple file stored on a remote server.

Is there anyway I could pre-filter the data I need and not run the "where" clause using the internal engine of PQ once all data have been imported ?

Thanks for your advice
Pat

[Anonymous · Accepted solution]

There is no way to "prefilter" a flat file as there would be no way to achieve such a thing. The only reason you can do this with a database is that you make a request to the data base server, which has a database engine running on it, which is able to do the filtering on your behalf and send you the result.

With a flat file, there is no engine running on the hosting server that could produce a stripped down version of the file to send you as part of a request.