SharePoint list filter

[geay]

In Power BI, I want to connect to a SharePoint list and filter the data that is imported. 

I need the filter to be applied before the data is imported.  The list has about 20,000 records and I only need about 200 items filtered by content type.

I have connected to the SharePoint list and then applied a filter on the column.  Does this filter import the results before or after importing the data?

[Anonymous · Accepted solution]

Any filters that you do as part of the Power Query, work on the data as passed to that specific line in the Power Query itself.  So the question goes back to, have you already downloaded those rows in earlier statements in your Power Query?  By the way you described it, the answer is likely yes.

If you are connecting to an SQL database, you have the ability to include an SQL statement, that can offer the ability to pre-filter the data as part of the connection.  This allows you to only source the minimum rows you require.

When connecting to a SharePoint list, you first connect to the site and then "Navigate" to the list contents itself.  Generally this navigation line of code looks like

Source{[Name="YourListsName"]}[Content]

Where [Content] is the column containing the whole table for each list

From what I can tell, there is no expansion parameters here that let you expand the table found in [Content] but only for certain rows.

When you filter in the next steps, Power Query will have already downloaded each row.

ALTERNATIVE.  I'm not sure if you are using On-Premise SharePoint or not, we have a database set up that gives me direct access to our SharePoint data.  Since its an SQL database, i'm able to make SQL statements to get access to everything I need.  This allows me to optimise what rows are returned.