Power Query won't read from .xls files

Question

I am using Office 2010. I have a query that combines data from several excel files in a folder. ".xlsx" files load fine, but when a ".xls" file exists in the folder, the query will not run (Gives error message: "Data could not be retrieved from database". In the query editor, when I click on the row for the file with an error, I see the message here: Error Message ). Resaving the files to ".xlsx" works, but I'd rather be able to use them as-is.

I have installed the MS Access Database Engine here: http://www.microsoft.com/en-us/download/details.aspx?id=13255 but it doesn't seem to help.

Any other ideas? Thanks! Edit: Added the two queries. First is the query applied to each file, second is the query that combines them.

Query "Transform Sample File from Supplier CMRTs":

let
    Source = Excel.Workbook(#"Sample File Parameter1", null, true),
    #"Smelter List_Sheet" = Source{[Item="Smelter List",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Smelter List_Sheet",3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Smelter Identification Number Input Column", "Metal (*)", "Smelter Look-up (*)", "Comments"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [#"Metal (*)"] <> null and [#"Metal (*)"] <> "")
in
    #"Filtered Rows"

Query "Supplier CMRTs":

let
    Source = Folder.Files("O:\Supplier CMRTs"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from Supplier CMRTs", each #"Transform File from Supplier CMRTs"([Content])),
    #"Filtered Rows" = Table.SelectRows(#"Invoke Custom Function1", each [Extension] <> ".txt"),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Supplier CMRTs"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Supplier CMRTs", Table.ColumnNames(#"Transform File from Supplier CMRTs"(#"Sample File")))
in
    #"Expanded Table Column1"

What does your query look like? You can see this by going to the Advanced Editor. — Alejandro Lopez-Lago - MSFT
If you make a new query and call Excel.Workbook with a path to an .xls file, what values show up in the Item and Kind columns (in that step, not the post-navigation steps)? The error you are seeing occurs when Power Query can't find the row with the cell values specified. In this case, it can't find a row with Kind=Sheet and Item=Smelter List. — Alejandro Lopez-Lago - MSFT

Marc Pincince Marc Pincince · Accepted Answer · 2017-11-07T16:18:09

I found that when I combine binaries, if I select the Sample Binary Parameter instead of a Sheet, and work my way from there, it will not balk at xls vs xlsx files. But before I could even get to the point where I could combine binaries for the folder, I had to filter only to xlsx files. Therefore, after I successfully combine the binaries, I have to go back to the Applied Steps and remove the one where I filtered only to xlsx files.

Here are some step-by-step with screen clips:

I started with 4 Excel Sheets in one Folder, called New Folder:

Here's what their data looks like:

Establish a new source from folder. Do not click Combine & Edit. Click the Edit button:

Filter the Extension column to only xlsx files:

Right-click on the column name for the Content column and then click Remove Other Columns, so you'll only have a Content column:

Click to combine the binaries. Then click the folder level Sample Binary Parameter and click OK:

Go to your Applied Steps and remove the Filtered Rows step, where you filtered to only xlsx files: Change...

to...

Also remove the Changed Type step from the Applied Steps, because it now won't work and isn't needed.

Now your query should work with both your xlsx and xls files.

For completeness, here's what I have at this step (all 4 of my files each have only one sheet, called Sheet1 in each, which is why you see 4 Sheet1 names):

Anyhow, the names dont matter for me, so I delete the Name column and expand the Data column to get:

You should recognize the data as the data from all 4 sheets above.

Power Query won't read from .xls files

1 Answers