Dynamic Folder files returning error

[Orfevre]

Hello, I have the below code where I am trying to create a dynamic folder path, however it is erroring when I go to combine the content with "Expression.Error: We couldn't find an Excel table named 'FolderPathVariables'." The source files clearly do not have an excel table named FolderPathVariables, how do I get this code to ignore looking for the table name and just return the data on the first worksheet? Have tried using {0}[Content] but still could not get it to work.

let
    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="FolderPathVariables"]}[Content]{0}[FolderPathVariable]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Receipts.xlsx")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"})
in
    #"Removed Other Columns"

 

[smozgur]

Folder.Files function requires a path parameter. The path value in the provided code is:

Excel.CurrentWorkbook(){[Name="FolderPathVariables"]}[Content]{0}[FolderPathVariable]

This function returns the FolderPathVariable column value from the first row in the table called "FolderPathVariables".

So, you basically need to have a table called "FolderPathVariables" in the current workbook (not in the source files) where this query is stored. And it should have at least one column named as FolderPathVariable that has the folder path value in the first row.
You can't ignore this step, because you need a path to look into it to get the "Receipts.xlsx" file.

By the way, just as a suggestion, from the code above I can say that you are trying to get a certain file content named as Receipts.xlsx likely from different folders. So, the following approach could be much better in this case instead of using Files.Folder (simply delete the last step and expand the column to get it with your own column names):

let
    FolderPath = Excel.CurrentWorkbook(){[Name="FolderPathVariables"]}[Content]{0}[FolderPathVariable],
    Source = Excel.Workbook(File.Contents(FolderPath & "\Receipts.xlsx"), null, true),
    KeepFirstRow = Table.FirstN(Source,1),
    KeepDataColumn = Table.SelectColumns(KeepFirstRow,{"Data"}),
    ExpandData = Table.ExpandTableColumn(KeepDataColumn, "Data", {"Column1", "Column2", "Column3", "Column4"})
in
    ExpandData

Or an alternative method if you don't want to deal with the column names:

let
    FolderPath = Excel.CurrentWorkbook(){[Name="FolderPathVariables"]}[Content]{0}[FolderPathVariable],
    Source = Excel.Workbook(File.Contents(FolderPath & "\Receipts.xlsx"), null, true),
    FirstWorksheet = Source{0},
    Result = Table.PromoteHeaders(FirstWorksheet[Data],[PromoteAllScalars=true])
in
    Result

You still need the following table named as "FolderPathVariables", according to your current method and how you want to make the folder path dynamic retrieved from the current workbook:

Book1
	A
1	FolderPathVariable
2	C:\Users\myname\folder1
Sheet1