Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

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:

 

plm2.JPG


Then I get the error as stated above.

12 REPLIES 12
Anonymous
Not applicable

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

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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"



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
v-juanli-msft
Community Support
Community Support

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.