Answered

Converting datetime from ISO 8601 format to dd/MM/yyyy

(0) Share

Report

Posted on by Ry009_

109

I have a Power Automate visual in Power BI that exports a CSV with field (that can include nulls) in ISO 8601 format (eg: 2024-07-03T14:00:00.000Z) that I would like to convert to dd/MM/yyyy.

I've tried several variations of the expression below, but keeps returning nulls, not sure what I am doing wrong?

if(empty(item()?['_Table[Datefield]']),null,formatDateTime(item()?['_Table[Datefield]'],'dd/MM/yyyy'))

This is my flow with the expression in the compose step;

And 'Outputs' from the Compose in the Append to array variable step

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

v-yetonggu-msft on at

Like (0)

Report
Hi @Ry009_ ，

Please try this expression:

if(empty(item()?['_Table[Datefield]']),'',formatDateTime(item()?['_Table[Datefield]'],'dd/MM/yyyy'))

If it still returns a null value, could you please show me a screenshot of your overall output _Table [Datefield] array?

Best Regards,

Sunshine Gu

Was this reply helpful? Yes No
Ry009_ 109 on at

Like (0)

Report

Thanks for the response and suggestion. Unfortunately nulls on the output 😞

Was this reply helpful? Yes No
v-yetonggu-msft on at

Like (0)

Report

Hi @Ry009_ ，

Is your array output empty too?

Best Regards,

Sunshine Gu

Was this reply helpful? Yes No
Ry009_ 109 on at

Like (0)

Report

Yeah.....not sure why? I've tested on a single row that isn't null for the field.

Was this reply helpful? Yes No
Verified answer

Ry009_ 109 on at

Like (0)

Report
I realized what I was doing wrong, I was referering the field as if I was using 'Run query against a dataset', rather than referencing the table array.

I also didn't bother with the compose as I had previously, and instead simply did the expression in the 'append to array variable' step.

Only problem is now I need to convert the date to local and not UTC, I'm not sure how to wrap it with the right timezone? (Aus Eastern Standard Time)

EDIT; Figured it out

Instead of
if(empty(item()?['DateFieldfromTableArray']),'',formatDateTime(item()?['DateFieldfromTableArray'],'dd/MM/yyyy'))

Amended it to

if(empty(item()?['DateFieldfromTableArray']),'',convertTimeZone(item()?['DateFieldfromTableArray'],'UTC','AUS Eastern Standard Time','dd/MM/yyyy'))

Was this reply helpful? Yes No