Limit number of records imported from Sharepoint

[GarlonYau]

Hi There,

I'm importing a dataset from a corporate Sharepoint list and it has over 900,000 records as the data goes back to 2002. This has made the refresh speed very very slow, sometimes taking up to 45 mins as I have multiple tables this size.

Only data that is 2018 till present is relevant to me.

Is there any way to limit the number of records that comes in, based on a date column to speed up my refresh times?

Here's what I have for the advanced editor.

let

    Source = SharePoint.Tables("Sharepoint URL removed for confidentiality reasons", [ApiVersion = 15]),

    #"918d9491-74f9-466c-a0f6-facb1ba85598" = Source{[Id="918d9491-74f9-466c-a0f6-facb1ba85598"]}[Items],

    #"Renamed Columns" = Table.RenameColumns(#"918d9491-74f9-466c-a0f6-facb1ba85598",{{"ID", "ID.1"}}),

in

    #"Renamed Columns"

Thanks for your help.

[v-eachen-msft]

Hi @GarlonYau ,

Power BI SharePoint connector doesn't contain any filter features currently. In my opinion, I'd like to suggest you to use OData connector(Sharepoint REST API). You could add conditions to query string to filter records before importing data.

Reference:

Working with lists and list items with REST

URL Conventions (OData Version 3.0)

[Tahreem24]

@GarlonYau ,

Do you have Date or Year column? If yes so filterout the all the date except from 2018 to till now in Power Query Editor.

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[GarlonYau]

I do have a date column. However I want to make sure that it gets filtered out before it even makes it into my imported dataset.

Example A.

Step 1: Imports 900,000 records

Step 2: Filter the records to only show 2018+

Example B

Step 1: Import 2000 records (only 2018+)

In example A, PowerBI still has to import those 900,000 records first before I can filter them. which makes refreshing the data really slow.