Need help changing date from mm/dd/yyyy to dd/mm/yyyy

[rovenne]

I need some help with a flow I've created. 

I'm using Run A Query Against A Dataset to export data from Power BI and create an excel file. However, in exporting the data from PBI, it seems to automatically change from the dd/mm/yyyy format in PBI to a mm/dd/yyyy format, which screws up the dates. So if Power BI has the date 09/04/2024 (april 9th), it will become 04/09/2024 (september 4th).

Is there any way I can change or fix this? 

This is my current flow:

Select > From:    outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']"

Select > Map:     item()?['ExportMO[Date]']

Create CSV table > From:   body('Select')

[ManishSolanki]

Hi @rovenne 

One way is to use expression to swap the date & month parts to get the correct date:

Below expression needs to be added in the expression box as highlighted in the above screesnhot:

concat(split(item()?['ExportMO[Date]'],'/')[1],'/',first(split(item()?['ExportMO[Date]'],'/')),'/',last(split(item()?['ExportMO[Date]'],'/')))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[rovenne]

Will this formula work for all dates?

Right now, it's changing dates when both day and month are small enough to be swapped (e.g. 09/04 -> 04/09), but I'm worried what will happen at higher dates (e.g. 15/04, since there is no month 15).

Will Power Automate choose to use the dd/mm/yyyy automatically at that point? Because in that case, your formula would stop outputting the correct date.

[ManishSolanki]

The shared expression will always swap the first & second parts of the date in 'mm/dd/yyyy' format.

[Robby_WW]

does this code not work in the "select action"?

formatDateTime(item()?['ExportMO[Date]'],'dd/MM/yyyy')

[rovenne · Accepted solution]

For some  unknown reason, the problem seems to have fixed itself. I don't know why it went wrong in the first place, but it appears to be working (for now?). So, I guess, problem solved?