How to get the result of a Folder.Files connector as the source of the Excel.Workbook connector?

[rcmv85]

How can I use the binary or the name of the file which results from the connector "Folder.Files" as the source of the Excel workbook connector?

Example: I have this source:

= Excel.Workbook(File.Contents("xpto"), null, true)

Now I would like to replace the "xpto" by the binary or the file path that I can get from a "Folder.Files" connector duly filtered to give me the file of interest. Is this possible?

[rcmv85]

Hi again @AlexisOlson !

I tried to adapt your approach but I'm having problems on my last step. Below my code:

SourceFolder = Folder.Files("C:\Users\rcmv\files"),
ExcelFileList = Table.SelectRows(SourceFolder, each Text.Contains([Name], "FinancialAnalysis")),
SortedList = Table.Sort(ExcelFileList,{{"Date created", Order.Descending}}),
FirstRow = Table.FirstN(#"SortedList",1),
RemoveOtherColumns = Table.SelectColumns(#"FirstRow",{"Name"})
ImportWorkbook = Excel.Workbook(#"RemoveOtherColumns",{"Name"}[Content])

The last step is not working. It gives me something like this:

Expression.Error: We cannot apply field access to the type List.
Details:
    Value=[List]
    Key=Content

 Any idea to solve this?

[AlexisOlson]

This should fix it:

let
    SourceFolder = Folder.Files("C:\Users\rcmv\files"),
    ExcelFileList = Table.SelectRows(SourceFolder, each Text.Contains([Name], "FinancialAnalysis")),
    SortedList = Table.Sort(ExcelFileList,{{"Date created", Order.Descending}}),
    #"Imported Excel Workbook" = Excel.Workbook(SortedList{0}[Content])
in
    #"Imported Excel Workbook"

The {0} refers to the first row (it's a zero-based index) and [Content] is the column that the Binary lives in.

[AlexisOlson]

Something like this?

let
    SourceFolder = Folder.Files("C:\Users\aolson\Downloads"),
    ExcelFile = SourceFolder{[Name="FileName.xlsx"]}[Content],
    ImportWorkbook = Excel.Workbook(ExcelFile)
in
    ImportWorkbook

As a single line:

Excel.Workbook(Folder.Files("C:\Users\aolson\Downloads"){[Name="FileName.xlsx"]}[Content])

[rcmv85]

Hi @AlexisOlson !

Thank you for your hint. I think I can work with that but how would do if you have more that one Excel file in that source folder and you wanted to select the most recent one?

[AlexisOlson]

Sort by last modified (or last created) and take the top row.

let
    Source = Folder.Files("C:\Users\aolson\Downloads"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")), /*Optional Step*/
    #"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Date modified", Order.Descending}}),
    #"Last Modified" = #"Sorted Rows"{0}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"Last Modified")
in
    #"Imported Excel Workbook"

If all of the files in the folder are Excel sheets, then the single line is:

Excel.Workbook(Table.Sort(Folder.Files("C:\Downloads"), {{"Date modified", Order.Descending}}){0}[Content])

[rcmv85]

Hi @AlexisOlson !

Thank you for you help. One last question. After the line:

#"Imported Excel Workbook" = Excel.Workbook(#"Last Modified")

I get something like this:

 But I would like to explore and process the contents of the Excel file. I've tried to replace that last line with something like this:

= Excel.Workbook(File.Contents(#"Last Modified"), null, true)

But I'm getting the error:

Expression.Error: We cannot convert a value of type Binary to type Text.
Details:
    Value=[Binary]
    Type=[Type]

How can solve this issue?

Thanks again for all the support!

[AlexisOlson]

Try using the GUI to click the Table text inside the cell of the Data column.