We help IT Professionals succeed at work.

Using power query in excel to combine 3 excel spreadsheets into one error field content of the record was not found???

I am using power query editor to import 3 excel workbooks into one workbook. I took these steps 1. Create new workbook 2. under – data – new query - from file – from folder (Path containing the 3 files) then transform then add column. 3. I created my new column name then added the formula Excel.Workbook([content]). I chose content in the choices
I get the following error message and it will not merge the files. Expression.Error: The field 'content' of the record wasn't found.
Details:
    Content=[Binary]
    Name=Address_MySql.xlsx
I get the same for each of the files I want to merge??? Not sure how to resolve
Comment
Watch Question

NorieAnalyst Assistant

Commented:

Patricia


Did you select Combine & Load after you'd selected the folder and the files were listed?

Commented:
What version of Excel are you using?

Author

Commented:
If am not sure what to do once I am in the combine load menu. I get
combine files
select object to be extracted
sample file: first file and a drop down with the spreadsheets --- Do I choose something here I can only choose one sheet at a time. Do I have to choose each sheet seperatly to load them into one master blank spreadsheet that I want to create??? Not sure the steps to take in this menu to load all my sheets into one master excel sheet... Perhaps there is a better path??? Thanks
I am using microsoft office professional 2016

Author

Commented:
When I go into account from the file menu and about excel I get a pop up the states about microsoft excel 2016 - so I think 2016 is the version. Hope that is right.

Commented:
Do me a favor.  Save the three files as .csv files and not .xlsx.  Then go to import from folder.  Click the down arrows above content.
NorieAnalyst Assistant
Commented:

Patricia


Have a look here for a guide to combining multiple Excel workbooks from a folder.


Combine files in a folder


Author

Commented:
I tried the above process. When I run the combine and load only one table from the worksheets gets loaded onto my new spreadsheet. The excel files are small as I condensed them for testing. In my workbook query (power query) I have a message that states loading data and it will keep running with no results. The other worksheets will not load. I will try the csv approach but I wanted to keep the formatting on the spreadsheets.
NorieAnalyst Assistant

Commented:

Patricia


When you follow the steps outlined in that link does everything described happen?


For example, do you see a list of files once you've browsed and selected the relevant folder?

Author

Commented:
Yes I do see a list of files. When saving the excel sheets as csv which fomat should I use ot sure what utf-8 csv is or should i use just csv? Thanks again. If helpful I can send an attachment

Commented:
I believe .csv works fine..

Author

Commented:
thanks why would someone use utf-8 anyway

Commented:
From the web..

UTF-8 can represent any character in the Unicode standard and it is also backward compatible with ASCII as well. It is the most preferred encoding for e-mail and web pages. It is the dominant character encoding for the world wide web.Aug 10, 2016

I have not had a use for this, so I am no expert here.

Author

Commented:
I have attached my steps. I do this before my combine and load
importing_combining_excel_sheets_ex.docx
Commented:
For one thing, M language is case sensitive.  excel.workbook should be Excel.Workbook..

Author

Commented:
Thanks got it finally

Commented:
Glad we finally got there..