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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors