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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
coathangers
Frequent Visitor

Dataflow to SharePoint folder to combine XLSX files Error: DataFormat.Error: Root element is missing

A dataflow that combines a folder of .XLSX files from a sharepoint recently stopped refreshing.

 

Dataflow > sharepoint folder > filter to folder > combine files

 

Now I get:

 

Error: DataFormat.Error: Root element is missing.

 

Every time I recreate this with the same permissions I get this error when it was working fine for about 1 year before. Has there been a recent update to change the way .XML is being accessed?

 

Please help.

1 ACCEPTED SOLUTION

I found the problem and it was one of the (100s of) files in the folder. I just now have to find out which one.

View solution in original post

4 REPLIES 4
otravers
Community Champion
Community Champion

It's not clear where in your query this error is occurring. Let's break it down into the smallest possible parts. Is your Source stage connecting to the SharePoint folder still working? Screenshots would help.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Thanks, there is nothing outside of basic data query functionality that I am doing here.

 

I create a "Sharepoint Folder" data query with a sharepoint connection that works fine for .csv file combining, but fails with these Excel files.

 

filter to the folder in Sharepoint (I have tested this with a fresh connection to Sharepoint numerous times). Then use the "Combine Files" feature in the content column. It creates the helper query and converts each file (they are all identical excel files and only excel files are in that folder).

 

combine-excel-error1.jpgcombine-excel-error2.jpg

 

It errors when I just refresh the flow, and I can trigger the error in the dataflow editor when filtering by string values:

 

combine-excel-error3.jpg

I found the problem and it was one of the (100s of) files in the folder. I just now have to find out which one.

You should be able to isolate the erroring row/file, probably best to do it from the Expanded step of your query.

https://radacad.com/exception-reporting-in-power-bi-catch-the-error-rows-in-power-query

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors