Forum Discussion
Dynamic filter on sharepoint files based on file name
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
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
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 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
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