cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Dynamic filter on sharepoint files based on file name

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?

2 ACCEPTED SOLUTIONS
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

Super User III
Super User III

@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 

 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

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

 

2020-10-05 16_16_37-Advanced Editor.png

 

2020-10-05 16_18_56-Untitled - Power Query Editor.png

but I can see that file when scrolling down

 

 

2020-10-05 16_17_18-Untitled - Power Query Editor.png

 

I removed "Wk" as it looks like case sensitive

Super User III
Super User III

@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 

 

SU18_powerbi_badge

View solution in original post

@AlB working, thanks!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors