Dynamic filter on sharepoint files based on file name

[mr_oli]

Hi there,
is there any way to create dynamic filter on sharepoint file list?

Every week there are 10 or more files uploaded with specific name into sharepoint.
Since beginning of year number of files are quite huge so I want to lower that number.

I want to create dynamic filter in query editor to download files only from current week and previous week.

Logic of file name is: “POWER customer CustomerName Wk41.2020.xlsx”
Is there any way to do that?

[AlB · Accepted solution]

Hi @mr_oli 

With Date.WeekOfYear(DateTime.LocalNow()) you can get the current week number. You can then build the substrings you want to look for:

currentWKNum = Date.WeekOfYear(DateTime.LocalNow()),

s1 = "Wk" & Text.From(currentWKNum) & ".2020",

s2 = "Wk" & Text.From(currentWKNum-1) & ".2020"

and finally use those substring in a filter operation to select only the rows with the names of the files you're interested in. I guess the first step when loading data from Sharepoint in PQ is a table with info on each file on each row, probably with a Name column (or similar) with the name of the file.

= Table.SelectRows(#"NameOfTableWithFilesInfo", each List.Contains( {s1,s2}, [Name]))

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @mr_oli 

With Date.WeekOfYear(DateTime.LocalNow()) you can get the current week number. You can then build the substrings you want to look for:

currentWKNum = Date.WeekOfYear(DateTime.LocalNow()),

s1 = "Wk" & Text.From(currentWKNum) & ".2020",

s2 = "Wk" & Text.From(currentWKNum-1) & ".2020"

and finally use those substring in a filter operation to select only the rows with the names of the files you're interested in. I guess the first step when loading data from Sharepoint in PQ is a table with info on each file on each row, probably with a Name column (or similar) with the name of the file.

= Table.SelectRows(#"NameOfTableWithFilesInfo", each List.Contains( {s1,s2}, [Name]))

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[mr_oli]

@AlB it looks great but to be honest I have no idea why it is not working - no result after implementing it 

but I can see that file when scrolling down

I removed "Wk" as it looks like case sensitive

[AlB · Accepted solution]

@mr_oli 

Apologies, I made a mistake in the filtering. It should be

Text.Contains([Name], s1) or Text.Contains([Name], s2)

instead of the

List.Contains({s1,s2}, [Name]) 

we were using

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[mr_oli]

@AlB working, thanks!