Unanswered

Power Automate incorrectly treating numerical column as date, not consistent!!

(1) Share

Report

Posted on by HA-15041256-1

I have a flow in Power Automate that connects to an Excel table in SharePoint. One of the fields returned is a Price field, which contains only numerical data to 2dp.

Earlier in the flow I use List rows present in a table to query the table, skipping the first 2,500 rows and using the Date Time parameter to choose ISO 8601 formatting.

When I use Select to choose the fields from the table, I specify float for my price column to ensure it is numerical.

However, I am encountering a weird error where List rows present in a table action is treating the Price column as an ISO date rather than a number, causing the action to break as it cannot convert to a float.

Weirdly, this does not happen consistently. I have double checked the source and there is only numerical data in the column. There are some rows (e.g. TradeDate) that I do want to treat as datetime so I do not want to remove the datetime format completely. Given this error isn't consistent, I'm struggling to work out why it happens.

Has anyone encountered this issue before or know why it is happening / how it consistently fix it? I've included below a screenshot of the Price field in excel, a screenshot of the Raw Output from the List rows action (confirming the date time format), and the Flow up to Select.

Categories:

Business Process Flows

All responses (6)

Answers (0)

ronaldwalcott 3,497 on at

Like (0)

Report

Power Automate incorrectly treating numerical column as date, not consistent!!

In the Excel sheet select the column and reformat as text then select it again and reformat as number and retest the flow,
creativeopinion 10,385 Super User 2025 Season 1 on at

Like (0)

Report

Power Automate incorrectly treating numerical column as date, not consistent!!

@HA-15041256-1 Have you tried switching to the Classic Designer? It might be an issue with the New Designer as it can still be buggy. I haven't seen this before.

In the meantime, you might be interested in these Power Automate + Excel YT tutorials.

Hope this helps!

Consider giving me a ❤️ if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram
HA-15041256-1 37 on at

Like (0)

Report

Power Automate incorrectly treating numerical column as date, not consistent!!

Hi Ronald,

The price column is being treated as a Date in every row. It is the way that entire column is being treated that is inconsistent.

For example, I have two scripts that are identical up to and including the List Rows action but they both report Price differently. See the images.

I'll try the Calculated Column approach though
ronaldwalcott 3,497 on at

Like (0)

Report

Power Automate incorrectly treating numerical column as date, not consistent!!

Not sure why it thinks it is a date but one approach would be to create a new calculated column in the excel table and convert the price to an integer by multiplying by 100 then dividing by 100 in the float to convert it back.

Do you know which row in the sheet is being treated as a date?
HA-15041256-1 37 on at

Like (0)

Report

Power Automate incorrectly treating numerical column as date, not consistent!!

@ronaldwalcott

Thanks for the swift response. The column is defined as a number in excel and changing this doesn't seem to have made a difference.
ronaldwalcott 3,497 on at

Like (0)

Report

Power Automate incorrectly treating numerical column as date, not consistent!!

In Excel what are the column type definitions?

You should ensure that the columns are defined correctly,