Parameter with a variable file name

[KDS]

I created parameters that pick up the path and file dates and plug them into my query.  

I have a table that pulls in the file path using the following formula: =LEFT(CELL("filename",B1),FIND("[",CELL("filename",B1),1)-1).  And then below it is a field where the person running the report would enter the file date (i.e., 7.12.2021 ABC Report.xlsx, they would enter 7.12.2021) .  The date would need to be changed each month when the query is refreshed.

I'd like to revise it so that instead of someone having to revise the date each month, it just picks up the file ending with "ABC Report.xlsx" and then all they have to do is refresh the query.

Here's what I currently have in my query:

let
Source = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parameter", type text}, {"Value", type text}}),
Path = #"Changed Type"{0}[Value],
ABC Report_RunDate = #"Changed Type"{2}[Value],
GetFiles = Excel.Workbook(File.Contents(Path & OpenCO_RunDate & " ABC Report.xlsx")),

.... and then I just continue one with all my other steps.

So in this example, the complete path would be something like C:\\Users\MyName\Documents\Project A\DataSource\7.12.21 ABC Report.xlsx.  I've tried using TextEndsWith but that didn't work.  Maybe I just didn't use it correctly or put it in the right place.

Note, there are are several other files within that folder, not just this one.

Thanks in advance.

[jennratten · Accepted solution]

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

[KDS]

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.

[OursPolaire]

Thank you @jennratten 🙂

If someone wants to do it from Sharepoint or similarly any other web URL with a simple filter.

let

    Source = 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"

[jennratten]

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
    importFileContent