Limiting data pulled into PBI desktop from Sharepoint list

[Wolf053]

Hi all, new to PBI and have a quick question which I am hoping has a relatively simple solution....

Is there a way to limit the amount of records initially pulled into PBI from each SharePoint list used as the data source? Refreshing PBI Desktop takes an age as it is importing tens of thousands of rows from each list, yet I really nearly only need the last 2 years worth of data. Is there some type of filtering that can be done within the Advanced Editor at the source level perhaps?

Thanks for any help, as Ive been pulling my hair out trying to find a solution!

[christinepayton]

The list queries can be linked to a specific list view, so you could create a list view in your list that has the filters you want and then connect to that. This is a pretty new feature I think, I've not had a use case to try it before. 

The other thing I do with large lists is to load them in a dataflow and then connect to the dataflow, this frontloads a lot of the processing. 

[Idrissshatila]

@Wolf053 ,true, so what you can do is that in power query click on the date column and click on filter and filter to show after a specific date like 1/1/2021, so the imported data to power bi will be filtered to get data after this date.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Wolf053]

@Idrissshatila , thanks for this - so would this mean when hitting refresh, only records that fall within the filter parameters are pulled and refreshed? Or will PBI still refresh by pulling all rows from SharePoint first, then filtering from there?  

[Idrissshatila]

@Wolf053 ,when refresh, power bi pulls all the data to power query and then filters to import only the data that falls in the filter.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Wolf053]

@Idrissshatila That makes sense - is there a way to not initially pull all the data into power query prior to filtering? 

[Idrissshatila]

@Wolf053, Then it should be in the datasource.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Idrissshatila]

Hello @Wolf053 ,

Try making incremental refresh.

check this https://youtu.be/Kui_1G6kQIQ

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Wolf053]

Hi @Idrissshatila ,

Thanks for the reply - unfortunately I dont have a pro licence, which I am pretty sure I need for incremental refreshing?