Power query - xls import error DataFormat.Error: External table is not in the expected format.

[ceckin]

Hi

i have searched for an answer high and low but unfortunately i am stuck. I have files coming out from SAP in XLS format. Up until last week i had zero issues using power query to merge all files from a folder.
Step 1: Specify Folder
Step 2: Filter only xls files
Step 3: Power query gives a warning that it does not like them and gives me the option to choose
Step 4: i choose CSV
and the world was fine.

Today when i try to merge i get DataFormat.Error: External table is not in the expected format.
After that i have not option to continue.

I have seen that i could be due to different versions of Power BI and Office (32 bit vs 64 bit). So i fixed that but it still remains.

If anyone can suggest something it would be amazing as i have been reading forums for couple of hours now and no luck

 

[ceckin]

Hi, so when i change the file extension from XLS to CSV it works fine as well. I am trying to figure out how to go back to how PQ used to behave

 

[ceckin]

Hi, also found this Force Power Query to Import as a Text File but it does not work for a folder for whatever reason

 

[Alex Blakenburg]

Can I assume you finished up with the PQ structure in the picture below ?
Do you want to start from scratch or do you have a lot of transformations you want to keep ?

If you get it to work using CSV files (you need at least 2) then the changes I made were as follows.

Query > Sample file
• You will need to change the source folder to what you are using
• After the CSV import the Filtered Rows step will either say = ".CSV" or <> something. Change it to = ".XLS"

let
    Source = Folder.Files("C:\Users\Documents\Software\Excel\Test\FromFolder"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".XLS")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    Navigation1 = #"Removed Other Columns"{0}[Content]
in
    Navigation1

Query > FromFolder
Same changes as above
• You will need to change the source folder to what you are using
• After the CSV import the Filtered Rows step will either say = ".CSV" or not <> something. Change it to = ".XLS"

let
    Source = Folder.Files("C:\Users\Documents\Software\Excel\Test\FromFolder"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".XLS")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

 

[ceckin]

Hi Alex,

thank you for taking the time. I cannot get to that structure. A week ago when i click edit it would allow me tell how to treat the files. At that point i would select CSV and it all worked.
Now i auto assumes and i cannot find a way how to tell PQ that yes these are XLS but you should import them as CSV.

Do i understand you correctly - Make it work for folder with CSV and then replace with XLS in the code only and not start from scratch ?

 

[Alex Blakenburg]

Yes. I got it to work by,
• Creating a copy of 2 of the XLS files and changing the file extension to CSV. (in the same folder)
• Import files From Folder
• Filter on just the CSV files and complete the Power Query import process and transformation.
This will now do all the steps based on them being CSV files.
• Then making the 2 changes in the Advanced Editor to change the filter step in both queries back to selecting ".XLS" files.

Queries in which to change the filter step after getting it to work with CSV extensions

 

[ceckin]

Hi

i tested the following. Renamed my files to have extensions CSV. Made it work
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
Then changed only to
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls")),

the other ones i did not touch and it worked. AMAZING! I will conduct tests now to confirm this as a viable solution

 

[ceckin]

Hi

manual how to fool Power Query
Step 1 - change at least two file extensions to from XLS to CSV
Step 2 - make it work with the Folder option for CSV
Step 3 - Close and load
Step 4 - Go back and change your file extensions from CSV to XLS
Step 5 - In advanced Editor change .csv to .xls
Step 6 - right click Sample file and again in advanced editor change the .csv to .xls
Step 7 - Enjoy

It is not perfect but it works

Special thanks to ALEX!

 

[ceckin]

Hi

i found an easier way!
Step 1 - Make it work with the folder option somehow!
Step 2 - Advanced editor for "Transform Sample file" replace
let
Source = Csv.Document(Parameter1,[Delimiter=" ", Columns=9, Encoding=1200, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
Step 3- Advanced Editor for "Sample file" fix the file extension to point to an actual file

BOOM and its done!

 

[Alex Blakenburg]

Thanks for the feedback. Happy I could help.