Converting the data from an xlsx file into a Json format

(0) Share

Report

Posted on by JL82

156

Hello Everyone,

I am trying to create a power automate flow that will upload a document into a website. The format that the website requires is a Json format and the data that i download from PowerBI into a onedrive folder is an xlsx file. I was able to get this done by using "select" action however, im having issues with the date/time. It only picks up the "Timestamp" values if its a text format, however excel automatically converts a cell to a date format whenever time is added. I keep getting errors that says cannot be converted to target type. Expressions i've used "formatDateTime(item()?['Timestamp'], 'yyyy-MM-dd HH:mm')", "formatDateTime(addDays('1899-12-30', int(item()?['Timestamp'])), 'yyyy-MM-dd HH:mm')" and "formatDateTime(item()?['Timestamp'], 'yyyy-MM-ddTHH:mm:ssZ')". I need help as i dont really know what to do from here.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report

Can you share the outputs of the Filter Array action? This will help troubleshooting this issue.

Additionally, can you also share the full expression. In the screenshot I can see you are using an if function in the expression as well. Would be good to see that part too to get the full picture of your setup.

Was this reply helpful? Yes No
JL82 156 on at

Like (0)

Report

Thank you.

The expressions ive tried using are below none of which works.

formatDateTime(item()?['Timestamp'], 'yyyy-MM-dd HH:mm:ss')

formatDateTime(item()?['Timestamp'], 'yyyy-MM-ddTHH:mm:ssZ')

formatDateTime(addDays('1899-12-30', int(item()?['Timestamp'])), 'yyyy-MM-dd HH:mm')

if(
or(equals(float(item()?['Timestamp']), float(item()?['Timestamp'])), isInteger(item()?['Timestamp'])),
formatDateTime(addDays('1899-12-30', int(item()?['Timestamp'])), 'yyyy-MM-dd HH:mm:ss'),
formatDateTime(item()?['Timestamp'], 'yyyy-MM-dd HH:mm:ss')
)

This below is the source file that im trying to test.

This is the output of the filter array

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,195 Most Valuable Professional on at

Like (1)

Report

I see the Timestamps values are formatted as a number, which is not ISO 8601 format.

Are you using the ISO 8601 format in your list rows present in a table action? If not, can you set that option (like in the screenshot below) and try again?

Was this reply helpful? Yes No
Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (0)

Report

formatDateTime(addDays('1899-12-30', int(item()?['Timestamp'])), 'yyyy-MM-dd HH:mm').

formatDateTime(item()?['Timestamp'], 'yyyy-MM-dd HH:mm')

formatDateTime(addDays('1899-12-30', int(item()?['Timestamp'])), 'yyyy-MM-ddTHH:mm:ssZ')

Was this reply helpful? Yes No