Forum Discussion
Importing data from folder
Thanks for your suggestion. The basic functionality (read from folder) works as it should.
The problem was something else as it appeared.
I use Parallels for Mac to use Windows/Excel on my Mac. The files were stored on iCloud Drive. This combination turned out to be the problem. Once I transferred the files to c:\temp and tried it from there, the issues were gone.
Hi lekkerbek,
If you want to load file from folder, you can try to use below method:
let
loadSource=(folderPath as text,fileName as text,tableName as text) as table =>
let
Source=Folder.Files(folderPath),
file = Source{[#"Folder Path"=folderPath,Name=fileName]}[Content],
#"Imported Excel" = Excel.Workbook(file),
Sheet = #"Imported Excel"{[Item=tableName,Kind="Sheet"]}[Data],
PromoteHeaders = Table.PromoteHeaders(Sheet)
in
PromoteHeaders
in
loadSource
Use:
Regards,
Xiaoxin Sheng
Anonymous
For importing tables from an Excel sheet, your method won't work (Kind = "Sheet").
What would the additional value be of your method, compared to the regular " files from folder" functionality, as your method reads only 1 sheet from 1 Excel file?
Otherwise lekkerbek also posted a similar question on a Dutch forum (although not mentioning the technical issues he encountered).
And I created this video for him in Dutch (although with the English version of Power BI Desktop):
For people not understanding Dutch, in the video the following steps are illustrated::
- files are read from a folder
- only Ecel files are read starting with "Leverancier" (and I illustrate this would also prevent issues from a file being open in Excel)
- then "combine binaries" is applied (the version that was introduced in November 2016), selecting all objects as each file may contain multiple tables,
- the exampe query is adjusted for filtering: first only tables are selected, next any tables with a name ending with "_totaal" are excluded, but only if the file contains multiples tables,
- then I show that the adjustments in the sample query are automatically copied into the function that is used for converting all files to tables.
- after some finishing touches I suggest to use "Detect Data Type" on the "Transform" tab to apply data types to the columns (that must be selected first), stating that these data types might still need to be verified / corrected.