Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.