Log In
Register
Home
New Member
February 3, 2016
Offline
Hi,
I am trying to run a power query to import data from a folder containing 5 monthly files but am getting the following message when I double click on 'Binary' column,
DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Details:
Binary
when I select one individual monthly file it imports data, any idea what I'm doing wrong.
regds,
D
See Answer
July 16, 2010
Offline
It sounds like you're using the technique for importing CSV or Text files from a folder. The technique for importing Excel files is different.
For Excel files from a folder you must Add a Custom Column and use the Excel.Workbook function.
=Excel.Workbook([Content])
Then you expand the Table to get the columns from the workbooks.
Mynda
Answers Post
New Member
February 3, 2016
Offline
thank you
Active Member
February 2, 2017
Offline
I also have the same error that comes up. The issue seems to be around the format of the date in the original files. The format is "dd-mmm-yyyy". When I go into the file and change it to a date format of dd/mm/yyyy I don't get an error on that data. If I have to go in and change the format, it is then pointless to use the Power Query.
July 16, 2010
Offline
Hi Ewan,
If your date format is different to that of the source data then you should be using 'Change Type Using Locale'. To do this, remove the first 'Changed Type' step in the applied steps pane. Then right-click the date column header > Change Type > Using Locale... Then select 'Date' and 'English (Australia)'.
Mynda
Active Member
May 31, 2021
Offline
This answers helps me very well, thank you..
But I need little bit more help, can I go/ extract/ return a specified Table in a specified Sheet too?
What function do I need?
July 16, 2010
Offline
Hi Oswin,
Welcome to our forum! When you close & load your query you can choose what sheet the table is loaded to.
If you're still stuck, please start a new thread with further information so we can help you.
Mynda
1 Guest(s)
