none
Running an append / combine query on a list of Excel files stored in a folder

    Question

  • Hello,

    Is there a way of running a Power Query that combines all required rows from a list of Excel files held in the same folder ? I noticed that there was a similar query regarding CSV files but I cant get the syntax right to do it for Excel files.

    Thanks,

    Nick

    Thursday, August 29, 2013 3:15 PM

Answers

  • Hi Nick,

    Another way to do this would be to create a query against a single file with all required transformation steps upfront (or maybe, just the ones needed in order to normalize the file to the desired tabular shape in PQ).

    Then, you can turn the query into a function and have the file path be the input for your query (basically, the input to "Excel.Workbook" library function call). An example on how to parameterize a query and turn it into a function is described in this (relatively old) blog post: http://blogs.msdn.com/b/dataexplorer/archive/2013/06/14/using-data-explorer-and-geoflow-to-decide-where-to-go-for-dinner.aspx

    Finally, you can use "From Folder" to retrieve the list of files, you will notice the "Path" and "Filename" columns in the result. You can multi-select these two columns and select "Combine Columns" to concatenate the contents (use "/" as delimiter), so you can build the full file path. Then, you can insert a custom column that invokes the function that you built. At that point, you will get nested tables into the custom column for each row, which you can expand using the "chevron" icon on the top-right corner of the custom column header. This will let you pick up the column(s) that you would want to expand and get a single tabular view across all these files.

    This would be future-proofed, meaning that you can refresh your query later and get the function applied to all new files that show up in this folder.

    Let us know if that works.

    Thanks,
    M.

    Wednesday, September 04, 2013 1:41 AM
    Owner

All replies

  • Hi Nick,

    Here is an example. In this example, say I have 3 Excel files under c:\1\1\1, they are named as 1.xlsx, 2.xlsx, 3.xlsx. With the query below, I can combine them together.

    let
        Source = Folder.Files("C:\1\1\1"),
        a1 = Source{[#"Folder Path"="C:\1\1\1\",Name="1.xlsx"]}[Content],
        ImportedExcel1 = Excel.Workbook(a1),
        Sheet1 = ImportedExcel1{[Name="Sheet1"]}[Data],
        a2 = Source{[#"Folder Path"="C:\1\1\1\",Name="2.xlsx"]}[Content],
        ImportedExcel2 = Excel.Workbook(a2),
        Sheet2 = ImportedExcel2{[Name="Sheet1"]}[Data],
        a3 = Source{[#"Folder Path"="C:\1\1\1\",Name="3.xlsx"]}[Content],
        ImportedExcel3 = Excel.Workbook(a3),
        Sheet3 = ImportedExcel3{[Name="Sheet1"]}[Data],
        Last = Table.Combine({Sheet1, Sheet2, Sheet3})
    in
        Last

    There may be even better way to do this. Please let us know if this helps.

    Regards

    Qunshu


    Clarification: Microsoft doesn't own any liability & responsibility for any of my posting.

    Saturday, August 31, 2013 3:16 PM
  • Thanks Andrew, but my problem is that I don't know the number or names of the Excel files in the source folder.

    Cheers,

    Nick

    Monday, September 02, 2013 6:58 AM
  • Hi Nick,

    Another way to do this would be to create a query against a single file with all required transformation steps upfront (or maybe, just the ones needed in order to normalize the file to the desired tabular shape in PQ).

    Then, you can turn the query into a function and have the file path be the input for your query (basically, the input to "Excel.Workbook" library function call). An example on how to parameterize a query and turn it into a function is described in this (relatively old) blog post: http://blogs.msdn.com/b/dataexplorer/archive/2013/06/14/using-data-explorer-and-geoflow-to-decide-where-to-go-for-dinner.aspx

    Finally, you can use "From Folder" to retrieve the list of files, you will notice the "Path" and "Filename" columns in the result. You can multi-select these two columns and select "Combine Columns" to concatenate the contents (use "/" as delimiter), so you can build the full file path. Then, you can insert a custom column that invokes the function that you built. At that point, you will get nested tables into the custom column for each row, which you can expand using the "chevron" icon on the top-right corner of the custom column header. This will let you pick up the column(s) that you would want to expand and get a single tabular view across all these files.

    This would be future-proofed, meaning that you can refresh your query later and get the function applied to all new files that show up in this folder.

    Let us know if that works.

    Thanks,
    M.

    Wednesday, September 04, 2013 1:41 AM
    Owner
  • Hi,

    I am running into the exact same problem having X similar (simple!) excel files located in the same folder and I need to import them all with one step

    to be honest, creating a function for this purpose is not really an option
    actually I was quite shocked that this does not work and I do not understand the reason why it should not work

    to be more precise, I the error message saying "DataFormat.Error: The input could not be recognized as a valid Excel document. Details: Binary"

    importing any of the excelfiles on its own works just fine using "From File --> From Excel"

    also my customers where very disappointed and confused that Microsoft does not even support multiple Microsoft Excel Sheets to be loaded together ?!?!?!

    any further feedback is highly appreciated!

    kind regards,
    gerhard


    - www.pmOne.com -

    Wednesday, September 04, 2013 5:51 PM
  • Hi,

    I am running into the exact same problem having X similar (simple!) excel files located in the same folder and I need to import them all with one step

    to be honest, creating a function for this purpose is not really an option
    actually I was quite shocked that this does not work and I do not understand the reason why it should not work

    to be more precise, I the error message saying "DataFormat.Error: The input could not be recognized as a valid Excel document. Details: Binary"

    importing any of the excelfiles on its own works just fine using "From File --> From Excel"

    also my customers where very disappointed and confused that Microsoft does not even support multiple Microsoft Excel Sheets to be loaded together ?!?!?!

    any further feedback is highly appreciated!

    kind regards,
    gerhard


    - www.pmOne.com -


    Gerard, is this a different problem? Thanks!

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, September 13, 2013 6:14 PM
    Owner
  • It is a very similar problem

    I need a way to import any number of Excel files stored in the same folder at once
    without creating any complicated M-function for each import

    Its just frustrating that you can import single Excel files and also multiple CSV-files but not multiple Excel files?
    and I think that's a very basic requirement for a lot of users and it is very hard to explain why an Excel add-in which supports multiple files to be imported at once cannot work with multiple Excel files

    so for me this is still an open issue

    kind regards,
    gerhard


    - www.pmOne.com -

    • Proposed as answer by nononono Wednesday, November 06, 2013 5:55 AM
    Monday, September 16, 2013 12:44 PM
  • just a little update, this still does not work with Power Query v2.9.3502.261 from my O363 subscription ...

    - www.pmOne.com -

    Tuesday, January 07, 2014 8:16 AM