locked
Use Data Explorer to import from a folder containing multiple Excel files

    Question

  • I have the following scenario: A shared folder contains multiple excel files, all based on the same structure. I would like to use Data Explorer to combine a named worksheet ("Volumes_flat_Y-1") in each file into one table in Excel. The list of files may change over time and is not known in advance.

    I have looked at the blog post from Chris Webb http://cwebbbi.wordpress.com/2013/03/01/importing-data-from-multiple-log-files-using-data-explorer/ and tried to replicate the "import from folder" procedure, but when I click on the combine button, the combinedbinaries step displays only the first Excel file. I can then select the desired table, but the end result is that data is imported from only one file, not from all the files in the folder.

    The generated code is below:

    let
        Source = Folder.Files("C:\test"),
        CombinedBinaries = Binary.Combine(Source[Content]),
        ImportedExcel = Excel.Workbook(CombinedBinaries),
        #"Volumes_flat_Y-1" = ImportedExcel{[Name="Volumes_flat_Y-1"]}[Data]
    in
        #"Volumes_flat_Y-1"

    Any idea on how to solve this ?

    Regards,

    Bertrand

    <object height="1" id="plugin0" style=";z-index:1000;" type="application/x-dgnria" width="1"><param name="tabId" value="{FA1CA1E4-D4DD-4E63-BE0B-DF84EB23D8B4}" /></object>
    Friday, July 05, 2013 8:07 AM