Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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?
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.
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])
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?
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])
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!
Try using the GUI to click the Table text inside the cell of the Data column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.