Help with the date format in Power Automate

[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!

[Agnius]

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-for...

-------------------------------------------------------------------------

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/

[apanta]

Hi Agnius,

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

[Agnius]

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.

[apanta]

Thanks for your reply, Angus!

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

[Agnius · Accepted solution]

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.

[apanta]

It worked.

Thank you so much 😊

[apanta]

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!

[Agnius]

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/

[apanta]

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.'.

[apanta]

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

[apanta]

is this happening because the "Completed Date" column has few null values? If so, how do we handle null values in this case?

[Agnius]

Yes, that can cause the error. Try handling this with an IF() expression like this:

if(equals(item()?['Completed Date'], null), '', formatDateTime(item()?['Completed Date'],'dd-MMM-yy'))

Note that the '' in the formula is two single quotes. This will pass on an empty string for the dates with null values.

-------------------------------------------------------------------------

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/

[apanta]

It worked. Thank you so much Agnius!

Also, is there any way I can get the previous month based on the current date (i.e. UTCNOW())? what I am trying to do is, save the csv file in the SharePoint as "Maintenance and Hospitality Requests for June - 2023" when I run the flow in July 2023.

[Agnius]

You can use the Add to time action and add -1 month to the current date. Then format the output of that action.

You could also use the addDays() expression, which allows adding a specific number of days to a date. You could do something like addDays(utcNow(), -int(formatDateTime(utcNow(), 'd'), 'MMMM - yyyy'). This should calculate the last day of the previous month and then convert it to a string based on the format provided.

-------------------------------------------------------------------------

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/

[apanta]

Everything's good now.
Thanks for all your help!
Have a great weekend 😊