Error combining files in Power query from Azure Datalake despite being excel files

[Anonymous]

I connected my powerbi to our Azure datalake and was able to load the folders into Power Query Editor and choose the excel files I wanted to combine and upload. However, I get an error when I click on 'Combine'.

Error message:

We didn't recognize the format of your first file (). Please filter the list of files so it contains only supported types (Text, CSV, Excel workbooks, etc.) and try again.

The error is straightforward however all the files are in xlsx format so the error makes no sense. Could it be because I loaded it through the azure datalake? When I do the same steps but the files live in Sharepoint, I have no problems so I'm assuming it is an issue with Azure files. Here is a snapshot of step before the error:

Then I get the error as stated above.

[Anonymous]

New Power BI User here. I'm have the same issue trying to combine .xlsx files from a OneDrive Business Folder. I can navigate to the folder but when I click combine i get

We didn't recognize the format of your first file (). Please filter the list of files so it contains only supported types (Text, CSV, Excel workbooks, etc.) and try again.

I know this is an old post but could anyone point me to a solution or tell me I'm not going mad and there is a bug?

Thanks

[Anonymous]

This was working. I had some of my reports set up in this method early last year. They are still running fine. Went to set up another report the same way today, and I keep getting the above error too. Haven't found a solution to my issue yet as I am accessing through SharePoint. 

Came across this if it helps - https://community.powerbi.com/t5/Community-Blog/Workaround-to-the-problem-in-the-dataflow-We-did-not...

[edhans]

Confirmed. I found I can click on the file binary an get to the data tables in the Excel workbooks, but I cannot Combine the binaries as I can from Sharepoint, Onedrive, etc. I am having no issues with CSV files combining from ADL.

I have Azure Data Lake Storage gen 1.

I can replicate the error in both the Nov 2018 Power BI Deskop app and Power Query in Excel from Office 365, Insider Build 1812.

[edhans]

Hopefully you can get this confirmed as a bug @v-juanli-msft. Note that there are others with this issue. See https://twitter.com/ehansalytics/status/1064965777755791360 for a conversation I had with someone that has written a blog about using Power Query to get files from Azure Data Lake. CSV works fine. Excel files fail. I have more details in this note too.

[v-juanli-msft]

Hi @Anonymous @edhans

Sorry for inconvenience. 

@edhans Thank you for providing such details, it is very generous of you to share.

I have reported this issue, i would come back once i get any information. 

Best Regards

Maggie

[edhans]

Any update on this @v-juanli-msft from the Azure team?

This is fundamentally broken. The only option is to export all Excel files to CSV, which really isn't ideal at all, especially when there are multiple tables in a single file and you want to combine based on table names within a file.

Downloading and working from local folder isn't an option either, as that 100% defeats the purpose of using Data Lakes to begin with.

[v-juanli-msft]

Hi @edhans

This issue also reproduced for the engineer.

They are working on fixing it.

If there is any update, i will come back to tell you.

Best Regards

Maggie

[edhans]

Excellent. Thanks. Hoping this is a quick fix (days, not weeks/months).

[v-juanli-msft]

Hi @edhans @Anonymous

It is informed that the bug will be fixed in the SU2 release with this change: 

https://powerbi.visualstudio.com/Power%20Query/_git/power-query/pullrequest/14591 

Unfortunately, there's no mitigation at this time, you'd have to essentially run what combine binaries is doing behind the scenes by yourself.

Best Regards

Maggie

[edhans]

What does that page say on timing? My github login ID doesn't have authority to access that page. I get a 401 error. "Sorry, but [your account] is not authorized to access this page"

[edhans]

FYI - I just tested this and it now appears to work. I have Excel 1902 Insider, but that shouldn't make a difference.

[v-juanli-msft]

Hi @Anonymous@edhans

I test as the article, still meet the same error as yours, i am going to confirm if it is a bug and researching for some workarounds.

for temporary workarounds, you could download files and connect to 'Excel files" or "folder" via Power BI Desktop. 

Best Regards

Maggie