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?
