Answered

Help with the date format in Power Automate

(0) Share

Report

Posted on by apanta

Hi,

I am new to power automate and struggling with the date format. I am trying to get the data from Power BI to CSV and save it on the SharePoint site. Please see the flow below:

The flow is working fine but my problem is on the extracted CSV file. The date is coming up on the long date format instead of the short format. In power BI, the date is in short date format but I am not sure why it is appearing like this on my extracted file. Is there something that I can do to fix this?

Also, with the file name, as I am using time stamp, can we do it in short date format as well? For example, in below picture, when the flow runs at 1st of every month (for example August), can we get name as "Testing New File July 2023"?

Any help would be greatly appreciated.

Thank you!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (15)

Answers (1)

Sort by

Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Try using the formatDateTime() expression to format it according to the format you need. See here for reference: https://learn.microsoft.com/en-us/troubleshoot/power-platform/power-automate/how-to-customize-or-format-date-and-time-values-in-flow

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

Was this reply helpful? Yes No
apanta 45 on at

Like (0)

Report
Copy link

Link copied!

Hi Agnius,

Sorry, I might sound silly, but how do I add it for the specific column?

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

When you've typed in formatDateTime(, you can click on the Dynamic content tab again and select the item you want, like you would normally.

Was this reply helpful? Yes No
apanta 45 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your reply, Angus!

But I can't see that column when I go back to dynamic content. Please see the below screenshot:

Was this reply helpful? Yes No
Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Try using formatDateTime(item()?['ColumnName'],'yyyy-MM-dd'))
Note you will need to replace ColumnName in there with an actual name of what you are trying to select from your dataset.

Was this reply helpful? Yes No
apanta 45 on at

Like (1)

Report
Copy link

Link copied!

It worked.

Thank you so much 😊

Was this reply helpful? Yes No
apanta 45 on at

Like (0)

Report
Copy link

Link copied!

Hi Agnius,

I am back again with the problem. The solution you provided worked well with the first date column. Since I have three date columns in my Power BI which I need as well, I tried to do same for rest of the two but the flow stopped working. I used the same formula but it didn't work for others. When I remove this formula from those two columns, it works fine but as soon as I put back the formula, my flow starts having some issue. Do you know if we can use this dateformattime function in only one column? Not sure why it's not working for other date columns.

Thanks!

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

No, you can use it on any date column. In order to help you more, I would need to see the error that it throws.
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.
If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

Was this reply helpful? Yes No
apanta 45 on at

Like (0)

Report
Copy link

Link copied!

Below is the error message:

The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"Ticket ID": "@item()?['Id']",
"Requestor Name": "@item()?['Requestor Name']",
"Requestor Date": "@formatDateTime(item()?['Requestor Date'],'dd-MMM-yy')",
"Priority": "@item()?['Priority']",
"Department": "@item()?['Department']",
"Status": "@item()?['Status']",
"Assigend Name": "@item()?['Assigned Name']",
"Short Description of the Issue": "@item()?['Short Description of the Issue']",
"Description of the Issue": "@item()?['Short Description of the Issue']",
"Facility": "@item()?['Facility']",
"Wing or Area": "@item()?['Wing or Area']",
"Level or Floor": "@item()?['Level or Floor']",
"Room Number/Name": "@item()?['RoomNumber/Name']",
"Target Completion Date": "@formatDateTime(item()?['Target Completion Date'],'dd-MMM-yy')",
"Completed Date": "@formatDateTime(item()?['Completed Date'],'dd-MMM-yy')",
"Completed By": "@item()?['Completed By']",
"Internal Notes": "@item()?['Internal Notes']"
}' failed: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

Was this reply helpful? Yes No
apanta 45 on at

Like (0)

Report
Copy link

Link copied!

is this happening because the "Completed Date" column has few null values?

Was this reply helpful? Yes No