Forum Discussion
PowerAutomate Extract From Power BI, CSV Date Format Issue (yyyy-MM-ddT00:00:00.000Z)
Hello,
I have a PowerBI report that we have setup for the purposes of exporting key data that we wish to compare changes in over time. I setup in PowerBI desktop a PowerAutomate flow which captures the report data into a CSV format, and then creates a file in a specified Sharepoint folder for the data, with a filename ending in the UTCNow() date.
However, I noticed that both within the file and within the filename, date values (columns setup as short dates) are being reflected in an odd date time format, shown below.
Date Modified
2023-12-04T09:19:01.000Z
It shows up like this in the output regardless of whether the value in the PowerBI report is set as a Date or as a Date and Time.
Is there any way for the CSV file to only capture the date without including this odd datetime formatting?
I would prefer not create custom columns in PowerAutomate, if possible, as the extract contains many columns of data (20+), and I also want to account for the possibility that the columns may in some small ways change over time.
Best regards!
You can use the formatDateTime() expression in PowerAutomate to format the date however you want. Here's the documentation on it: How to customize or format Date and Time values in a flow - Power Automate | Microsoft Learn
5 Replies
You can use the formatDateTime() expression in PowerAutomate to format the date however you want. Here's the documentation on it: How to customize or format Date and Time values in a flow - Power Automate | Microsoft Learn
Christine, I tried using the formatDateTime() function to take care of this into an Excel export, but am receiving an error message that it cannot convert the date into a string or something. Do you happen to know a workaround to this?
The formula I used was as follows:
formatDateTime(items('Apply_to_each')?['Date Modified'], 'MM/dd/yyyy')In searching around about this error it indicates that it occurs when a field value is null or empty. However, the odd thing is that all of the date fields (there are 3 columns either as date or date/time) have values included. I don't see any null values.
Yeesh, you are looping over 1000 rows - that is a lot for a loop in Power Automate. What was the purpose of the flow?
If you're running into issues with the date field potentially being null, I would stick it in a "condition" step and set the condition to the field being not equal to null (as an expression) and stick the Excel step in the "yes" branch. If not equal to null doesn't do it, try IsBlank(your field name) not equals true (just the word true, lowercase) instead.
I think this should be formated in the flow you created in Power Automate.
