Excel add row into a table, number saves as text.

[Alm3n]

Hi,
I´m running a flow which transforms a JSON from Power Apps into a temporary excel file and then email the file to a group of users.
But I have problem with the locale of the excel file and the fact the output of "add row to table" is formatted as text.

The app use number format 0,00. When the apps convert to JSON and send it to Power Automate the format becomes 0.00. The comma "," becomes a point/full stop ".".

When Power automate adds the row to excel the values become text which becomes a problem for the receiving part.

I am not sure the comma/period actually is the problem since integer values also get formatted like text in the output of "add row to table"

Is there a way to change the format in the excel table to correct format in the flow?

ParseAdd rowExcel

Flow part 1Flow part 2

[AnthonyAmador]

Hi @Alm3n 

There is no action to change the format of an Excel table from Power Automate. You have to change the format in the Excel app, however I did some tests and it seems that when you create a new table, the cells have the "General" format by default, this means that it can recognize what you add if it is a text or a number and format it accordingly, so I'm not sure why you experience this behavior. 

Can you share how you edit the action "Add a row into the table"?

Kind regards. 

[Alm3n]

Hi @AnthonyAmador

I just noticed that if I resubmit the flow then the formatting is correct in the second file. 

The edit action looks like this:

[AnthonyAmador]

I don't see any problem with your flow, it's strange behavior because at least the value of "Antal pass" is recognized as a number, it could be due to the comma, but I'm not sure why your action changes it because in my repro it doesn't, even if it is a string, the table recognizes that it has a number. 

If you would like to create a ticket with Microsoft Customer Support here is a link on how to do so: Get Help + Support - Power Platform | Microsoft Docs

Hope this helps.

Kind regards. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alm3n]

Okay, I have created a ticket so hopefully they can solve this.

Thank you for your help 2 @AnthonyAmador!

[tcbaker]

@Alm3n, I am having a similar issue, did you ever find a resolution?

[Alm3n]

Hi @tcbaker,

I have had a meeting with both a support engineer from power automate and an excel engineer. They are currently working under the assumption that there is a problem within Power Automate. So I have an ongoing ticket but no solution yet.

I have noticed that the step "Add row into table" acts differently when the flow runs the first time versus when I resubmit the flow. If the flow contains a decimal number on the first execution the output of this step will have a comma ",". When excel open this file the values will not be as expected. 
However, when I resubmit the decimal number will instead have a full stop "." and when excel open this file the values are as expected.

I think the output from Power Automate always need to be a full stop, and then Excel can convert it to comma based on locale.

[Alm3n]

@tcbaker, I just received some new information about this. Apparently they are about to release an updated version up the "Add row into a table". I got not specific ETA except "soon".

[tcbaker]

@Alm3n Thanks for the update.  I did notice in my issue, the data that is added to the row, seems to have a line break in each cell.  If i manually remove the line breaks, it seems to respect the cell formatting.  Hopefully the update will address this.