Power Query search cell to find specific file in folder?

[Wolfspyda]

Just wondering as the title suggests if its possible to have a search cell to place the name of the sheet you want from a specific folder? I have seen you can do a search option within power query, but that will be difficult for others.

Essentially, I have a folder that has multiple files and I only want to extract the file I want, eg im looking for the "Animals" file, so in cell A1 I type "Animals". This is the first step, so I need to know if its even possible to do this before doing a custom sort of the data again based on some other cell information.

 

[Ron Coderre]

Not quite enough to go on...but, I took a shot with this example:

- Name a cell "SearchPath" and enter the path want to search
(I used: C:\Coderrer\Excel_PowerQuery\)

- Name a cell "SearchTerm" and enter the file name word(s) you want to find
(I used Increment)

- Create Power Query function that reads a value from a named cell.

Query Name: fnGetNamedCellVal
Query Code:
(ParameterName as text) =>
let
     ParamSource = Excel.CurrentWorkbook(){[Name=ParameterName]}[Content],
     Result = ParamSource{0}[Column1]
in
Result

- Last, this query searches the SearchPath for file names containing the SearchTerm.
If only one file matches, the table in that file is loaded.
Otherwise, no data is loaded.

Query Name: GetFileData
Query Code:
let
    PathToSearch = fnGetNamedCellVal("SearchPath"),
    TermToFind = fnGetNamedCellVal("SearchTerm"),
    Source = Folder.Files(PathToSearch),
    KeepRowsContainingSearchTerm = Table.SelectRows(Source, each Text.Contains([Name], TermToFind)),
    CountOfMatchingRows = Number.From(Table.RowCount(KeepRowsContainingSearchTerm)),
    ConnectOrStop = 
        if CountOfMatchingRows <> 1
        then Table.Skip(KeepRowsContainingSearchTerm,CountOfMatchingRows)
        else KeepRowsContainingSearchTerm,
    GetTheWorkbook = try Excel.Workbook(ConnectOrStop{0}[Content]) otherwise null,
    KeepTheSelectedTableRow = try Table.SelectRows(GetTheWorkbook, each ([Name] = "MyDataWithIncrIndex")) otherwise null,
    GetTheData = try Table.ExpandTableColumn(KeepTheSelectedTableRow, "Data", {"Name", "Status", "Index", "IncrIndexByName"}, {"Name.1", "Status", "Index", "IncrIndexByName"}) otherwise null
in
    GetTheData

That's not a finished product....It's just something that might get you started.

 

[Wolfspyda]

excellent. ill give that ago.

 

[Wolfspyda]

I had some issues with trying this. I tried a couple of other methods as well, as I found a YouTube video that I thought might do what I wanted.

See the link below.

https://www.youtube.com/watch?v=KVqprz4Rd3I