Power Automate adds unwanted XML data to Excel

[Bubulover]

Hi all !

I've an issue with a flow that is working, BUT the output file isn't convenient. 

My flow is pretty simple:

• When I receive a specific email (sender, subject,...),
• I delete an existing file in a sharepoint,
• I get email attachment - which is an Excel file,
• Then I create a new file in the same sharepoint location.

It's a simple "Replace" flow, with deleting and creating.

My problem is that the created Excel file owns XML data (that I can identify using the Excel - File - Info - Inspect Document function).

These XML data makes the daily automated refreshes of a PowerBI report fail.

Since the Excel file opened directly from the email attachment doesn't have XML data, I suspect that they're added by the Power Automate flow.

Does someone have an idea about how can I prevent these XML data to be added?

Or perhaps to remove them at the end of my flow?

Thank you in advance for your help !

[lbendlin]

What type of Excel file is this?  (Power BI prefers .xlsx).  Could you potentially post a before and after file demonstrating the issue?

[LT3]

I'm having this exact same issue.

Files are in .xlsx format before and after the Power Automate process, but when I inspect after the Power Automate has occured they contain custom XML Data which is causing errors with Power BI service.

Was working fine previously.

[lbendlin]

I know that PowerApps are injecting hidden (and unwanted) index columns but I didn't think Power Automate would do the same?

[Bubulover]

Both files are in .xlsx

Do you know how can I post file?

If PowerAutomate mandatory adds XML data to .xlsx files added in a sharepoint, is there any trick that might prevent it?

[LT3]

Can I ask if you have a custom name in the File Name part of the Create File action in Power Automate?

Mine works ok as long as I don't change this File Name field from 'Name' (attachments name) to any custom expression or text!?

[Bubulover]

Yes I do change the file name at the Create File action.

I'll change the action to use the original file name, try again, and I'll let you know.

[Bubulover]

I indeed changed the file name at the Create File action of Power Automate.

I've changed the flow that now keeps the original file name at this action.

But I'm still having the issue at the PowerBI refresh.

Any other idea ?

[LT3]

Sorry, probably not much help, but I ended up changing my source file from .xlsx to .csv format and then the process worked as it should.

Not sure if that's an option for you.

[Bubulover · Accepted solution]

Hello,

The file is actually sent by an outside compagnie that isn't willing to change it.

My understanding is that to convert an .xlsx to .csv, Power Automate needs that the .xlsx owns a Table with a specific name, which the file doesn't have.

So, I follow the below video to manually add Table in this file, then I could use the 2nd video to convert the .xlsx to a .csv

And yes, you're right, .csv doesn't carry the XML data that PowerAutomate adds and for now my problem is solved!

Create Table in .xlsx with Power Automate:

Create Table in Excel WorkSheet - DamoBird365

Convert .xlsx to .csv:

Microsoft Power Automate: How to convert Excel (.xlsx) to .csv files - Full Tutorial - Bing video

[LT3]

Just here to say thanks for linking those, just adding the script part to create a table within the worksheet worked for me. I had another issue with my flow because the csv files ended up being too large to email!

[Bubulover]

Perhaps you can share them by posting on online location such as OneDrive or Sharepoint ?

[Raphaelle_J]

Hi,

Since 10 days or so, i have been having the same problem with one of my power automate flow.

How and why PA adds this unwanted custom XML data baffles me...

My flow is the following :

1.  I receive an excel file by mail, which is moved to a specific folder
2. PA monitors this folder, and when a mail with an attachment is received, the attachment is placed into a specific Sharepoint Folder
3. The xlsx. is used as a data source for a Power BI report.

The auto-refresh for this report worked fine, until it didn't...

I checked at step 1 and there is no custom XML data at this point. It is added at the time the file is being sent to Sharepoint.

I discussed the issue with Power BI support on the phone, and we concluded that Power BI, nor the excel file provider were the problem.

Unfortunately, not having a Power Automate premium plan, i cannot open a ticket with Power Automate support.

Is there any solution easier than having to convert my excel file to a .csv ? Maybe a way with to delete custom XML data with Power Automate ?

regards,

[LT3]

I did end up managing to put a work around in where the Power Automate flow opens the file and runs a script converting the data into a table in Excel Online which Power BI was happy with.

Here's the article that I found super helpful:
Create Table in Excel WorkSheet - DamoBird365

[Raphaelle_J]

Thank you for you reply.

I found another workaround while reworking my query for the nth time :

When configuring the Sharepoint source, instead of clicking on "Binary" as i did previously, you just click on the double arrows next to "Content".

Then, it triggers "Helper requests" which are set automatically, and you work your query from there.

In the end, if the source file is the exact same that with the "binary" method, this time it works.

Regards.