Editing exiting Workbook query Connection SQL Statement to limit columns and rows - Power Query

[Anonymous]

Hi,

I am pulling data from a 12 mb CSV file that contains 300k rows and 8 columns. I want to limit the number of columns pulled into Power Query editor to only the last 2 columns `PunchID`, `IdeaID` and also limit the number of rows to certain `PunchID's` like `-1116`,`-1115`,`-1114`,`-1113`,`-1111`. I don't want to do it from the Power Query editor but by modifying the default SQL query that gets generated when a query connection is created in Workbook (Data > Connections).

The Connection String is :

Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="Test";Extended Properties=""

The default SQL in the Connection properties for the query:

SELECT * FROM [Test]

What i want to do is change the Connection String to detect Headers and Column Datatypes :

Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="Test";Extended Properties="IMEX=1;HDR=YES;"

and SQL query string to limit Rows and Columns :

SELECT [PunchID],[IdeaID] FROM [Test]
WHERE [PunchID]='-1116.0' OR [PunchID]='-1115.0' 
OR [PunchID]='-1114.0' OR [PunchID]='-1113.0' 
[PunchID]='-1111.0'
ORDER BY [PunchID] DESC,[IdeaID] ASC

However, the query when opened in the Power Query editor shows `Source` displaying all the columns and rows. There is no error, but SQL is definitely not working! How do i limit the data before pulling into Power Query? Is this possible to do or am i missing something?

[edhans]

I'm not understanding. You cannot use SQL statements with CSV files. There is no server to process the request and limit what you get. With flat files like Excel and text file, you must do 100% of your processing in Power Query's editor.

[Anonymous]

I was of the assumption that since i could use ADODB to query and pull selective records from a csv, it would work similarly with OLEDB for Mashup as the SQL is still querying the csv data using a select statement in the connection. Not sure, why it should not work straight away like ADODB SQL queries on csv files.