none
Issue with PowerQuery for Excel Combine Binaries RRS feed

  • Question

  • I am Trying to use Power Query for Excel to Combine data from different Excel files in a folder  into a single Table,

    I have a Query that works for any Single file , but when I select Move than One file the Combine Binaries  result in the following message

    DataFormat.Error: The input couldn't be recognized as a valid Excel document.
    Details:
        Binary

    again - the query works if every file is selected individually,  but if selecting more than one file the combine fails

    Monday, May 9, 2016 9:36 PM

Answers

  • If you do an online search for "power query multiple excel workbooks" you'll get quite a few useful links. Here are some that I found:

    http://dutchdatadude.com/combining-excel-files-using-power-query-for-excel/

    http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

    • Marked as answer by Dave Pitts Tuesday, May 10, 2016 5:55 PM
    Tuesday, May 10, 2016 2:13 PM

All replies

  • Hi Dave. If you combine the binaries before asking M to interpret them as Excel, you're going to run into trouble since concatenating multiple xlsx files together doesn't result in a valid xlsx file.

    Instead, you should apply your "single file" operations to each file individually, and then concatenate the results using Table.Combine.

    Let me know if that helps.

    Ehren

    Monday, May 9, 2016 11:29 PM
    Owner
  • Hi  Ehren.

    Thanks for the response, I not sure I total Understand what you are saying though, or more explicitly how I would inplement that.

    I have a Bunch of files out there  each has up to seven sheets with data on that I need to consolidate into a  Single Table, So even though when I say Import from a Folder, It shows me all the Excels that are there, (and each are selected as Default) I cant actually Combine the Binaries into one?, I have to select each one individually create a Query that Gives me a Table and Combine the tables? This sounds like the Option Shouldn't even be available if I select Excel Files. Do you know what binaries can be Combined?

    Dave

    Tuesday, May 10, 2016 12:41 AM
  • If you do an online search for "power query multiple excel workbooks" you'll get quite a few useful links. Here are some that I found:

    http://dutchdatadude.com/combining-excel-files-using-power-query-for-excel/

    http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

    • Marked as answer by Dave Pitts Tuesday, May 10, 2016 5:55 PM
    Tuesday, May 10, 2016 2:13 PM
  • Hi Dave. Regarding your question about why Combine Binaries is even an option, there are files that can be concatenated to create a valid result. One example that comes to mind is CSV or other delimited files.

    Ehren

    Tuesday, May 10, 2016 5:05 PM
    Owner
  • Curt

    Thanks for the links - The second one did the trick, just what I needed

    Tuesday, May 10, 2016 5:55 PM