Forum Discussion
Parameter with a variable file name
Hello - this is definitely doable. It can be done sucessfully on an ongoing basis if you come up with a consistent file naming convention - for example, based on your sample file name of 7.12.21 ABC Report.xlsx, your naming convention could be '[Date] [FileName].[FileExtension]', and if so, you could set your query evaluate text in the file name that appears after the first space.
Then you could retrieve the file like so... In the query, I have added comments explaining what's going at each step.
let //------------------------------------------------------------------- // Set some variables. //------------------------------------------------------------------- // Path to the file's folder. Be sure to include the slash at the end. folderPath = "Your Path Goes Here\", // File extension. fileExt = "xlsx", // Character in the file name that separates the date and the text to find. fileNameDelimiter = "_", // Text that appears after the file name delimiter, excluding the file extension. fileNameKeyphrase = "demographics_analysis_data", //------------------------------------------------------------------- // Transformation steps. //------------------------------------------------------------------- // Retrieve all files in the folder. folderContents = Folder.Contents ( folderPath ), // Limit the files to only those meeting the file extension criteria. Evaluate extensions case insensitively. filterExtensions = Table.SelectRows(folderContents, each Text.Contains([Extension], fileExt, Comparer.OrdinalIgnoreCase)), // Add a new column with text appearing after the delimiter and before the file extension. textBtwnDelimiters = Table.AddColumn(filterExtensions, "File Name Keyphrase", each Text.BetweenDelimiters([Name], fileNameDelimiter, [Extension]), type text), // Limit the files to only those containing the file name keyphrase. // To evaluate case insensitively... // Option 1: Use Text.Contains with the optional argument of Comparer.OrdinalIgnoreCase // Option 2: Use equals and wrap both the keyphrase variable and the file name text in Text.Lower(). //filterFileNames = Table.SelectRows(textBtwnDelimiters, each Text.Contains([File Name Keyphrase], fileNameKeyphrase)) filterFileNames = Table.SelectRows(textBtwnDelimiters, each Text.Lower ( [File Name Keyphrase] ) = Text.Lower ( fileNameKeyphrase )), // Create a way to narrow the results to only one file, should multiple files be returned. filterFileCreatedDates = Table.SelectRows(filterFileNames, let latest = List.Max(filterFileNames[Date created]) in each [Date created] = latest) in filterFileCreatedDates
Hello - this is definitely doable. It can be done sucessfully on an ongoing basis if you come up with a consistent file naming convention - for example, based on your sample file name of 7.12.21 ABC Report.xlsx, your naming convention could be '[Date] [FileName].[FileExtension]', and if so, you could set your query evaluate text in the file name that appears after the first space.
Then you could retrieve the file like so... In the query, I have added comments explaining what's going at each step.
let
//-------------------------------------------------------------------
// Set some variables.
//-------------------------------------------------------------------
// Path to the file's folder. Be sure to include the slash at the end.
folderPath = "Your Path Goes Here\",
// File extension.
fileExt = "xlsx",
// Character in the file name that separates the date and the text to find.
fileNameDelimiter = "_",
// Text that appears after the file name delimiter, excluding the file extension.
fileNameKeyphrase = "demographics_analysis_data",
//-------------------------------------------------------------------
// Transformation steps.
//-------------------------------------------------------------------
// Retrieve all files in the folder.
folderContents = Folder.Contents ( folderPath ),
// Limit the files to only those meeting the file extension criteria. Evaluate extensions case insensitively.
filterExtensions = Table.SelectRows(folderContents, each Text.Contains([Extension], fileExt, Comparer.OrdinalIgnoreCase)),
// Add a new column with text appearing after the delimiter and before the file extension.
textBtwnDelimiters = Table.AddColumn(filterExtensions, "File Name Keyphrase", each Text.BetweenDelimiters([Name], fileNameDelimiter, [Extension]), type text),
// Limit the files to only those containing the file name keyphrase.
// To evaluate case insensitively...
// Option 1: Use Text.Contains with the optional argument of Comparer.OrdinalIgnoreCase
// Option 2: Use equals and wrap both the keyphrase variable and the file name text in Text.Lower().
//filterFileNames = Table.SelectRows(textBtwnDelimiters, each Text.Contains([File Name Keyphrase], fileNameKeyphrase))
filterFileNames = Table.SelectRows(textBtwnDelimiters, each Text.Lower ( [File Name Keyphrase] ) = Text.Lower ( fileNameKeyphrase )),
// Create a way to narrow the results to only one file, should multiple files be returned.
filterFileCreatedDates = Table.SelectRows(filterFileNames, let latest = List.Max(filterFileNames[Date created]) in each [Date created] = latest)
in
filterFileCreatedDates
Thanks for your help, but I don't see how I get the data. The only field that has the "expand" button is the Attributes column. The table that's left once I get to the filterFileCreatedDates field shows only the following columns:
- Content
- Name
- Extension
- Date accessed
- Date modified
- Date created
- Attributes
- Folder Path
- File Name Keyphrase
I'm a bit of newbie to this so apologies if it seems like a silly question.
Thanks so much for the comments. Very helpful.
No problem! We were all newbies at some point! Here is a new script with a few more steps at the bottom. The script below should have the same result as your GetFiles step.
let //------------------------------------------------------------------- // Set some variables. //------------------------------------------------------------------- // Path to the file's folder. Be sure to include the slash at the end. folderPath = "Your Path Here\", // File extension. fileExt = "xlsx", // Character in the file name that separates the date and the text to find. fileNameDelimiter = "_", // Text that appears after the file name delimiter, excluding the file extension. fileNameKeyphrase = "demographics_analysis_data", //------------------------------------------------------------------- // Transformation steps. //------------------------------------------------------------------- // Retrieve all files in the folder. folderContents = Folder.Contents ( folderPath ), // Limit the files to only those meeting the file extension criteria. Evaluate extensions case insensitively. filterExtensions = Table.SelectRows(folderContents, each Text.Contains([Extension], fileExt, Comparer.OrdinalIgnoreCase)), // Add a new column with text appearing after the delimiter and before the file extension. textBtwnDelimiters = Table.AddColumn(filterExtensions, "File Name Keyphrase", each Text.BetweenDelimiters([Name], fileNameDelimiter, [Extension]), type text), // Limit the files to only those containing the file name keyphrase. // To evaluate case insensitively... // Option 1: Use Text.Contains with the optional argument of Comparer.OrdinalIgnoreCase // Option 2: Use equals and wrap both the keyphrase variable and the file name text in Text.Lower(). //filterFileNames = Table.SelectRows(textBtwnDelimiters, each Text.Contains([File Name Keyphrase], fileNameKeyphrase)) filterFileNames = Table.SelectRows(textBtwnDelimiters, each Text.Lower ( [File Name Keyphrase] ) = Text.Lower ( fileNameKeyphrase )), // Create a way to narrow the results to only one file, should multiple files be returned. filterFileCreatedDates = Table.SelectRows(filterFileNames, let latest = List.Max(filterFileNames[Date created]) in each [Date created] = latest), getFileContent = filterFileCreatedDates{[Name=filterFileCreatedDates[Name]{0}]}[Content], importFileContent = Excel.Workbook(getFileContent) in importFileContentThank you jennratten 🙂
If someone wants to do it from Sharepoint or similarly any other web URL with a simple filter.
letSource = SharePoint.Files("https://YourSharePointSite/sites/YourFolder/", [ApiVersion = 15]),
#"FilterFileNames" = Table.SelectRows(Source, each Text.Contains([Name], "YourFilter")),
getFileContent = #"FilterFileNames"{[Name=#"FilterFileNames"[Name]{0}]}[Content],
importFileContent = Excel.Workbook(getFileContent),
#"Raw Data_Sheet" = importFileContent{[Item="Raw Data",Kind="Sheet"]}[Data]
in
#"Raw Data_Sheet"
