Forum Discussion
PowerAutomate Extract From Power BI, CSV Date Format Issue (yyyy-MM-ddT00:00:00.000Z)
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:
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.
Our leaders want to capture and archive project forecast data at a certain point each month for historic archival and reporting purposes. We have project forecast documents that're uploaded into PowerBI in an earlier dataflow. They want to see how forecasts are changing month over month, or how purchases and resource dollars are being moved around (kicking the can down the road, so to say). The PowerAutomate is meant to extract this forecast data and upload it into an Excel file for storage, for later reporting. I am going to build a report that calculates the month to month variance as a percentage or dollar amount, and create some views that let them look at it from different levels (project type, project manager, sponsor, etc.) to see if there are trends in projects where money is constantly being reshuffled and identify ways to improve cash flow expectations.
The 1000+ rows represents project name, project spend categories (IOs, GLs, etc), the dollars associated, and the DATE when the extract is occurring. Since project files are constantly being updated by our various PMs we have to identify the point in time each month, or in the month prior, where the forecast data is considered "most accurate".
I have tried this with two flows, one extracts the data as a CSV in one bulk export but separate files (takes less than a min to complete) and this one is intended to extract the data and perform a FOR EACH function to add the data as rows into one consolidated excel file.
I think the XLSX approach is more favorable because I can capture all yearly extractions in one file (once per month, for one year, so appx 12K - 15K rows of data), and then use fabric to transform it as necessary, rather than pulling in a bunch of CSVs from a folder.
I would note that the date formatting issue exists in both. It's so odd, because I haven't had experience with this particular format before. Excel does NOT recognize or translate it when the column is changed to short-date or date-time.
I think that in the excel version I have to use your recommended expression with a FOR EACH clause for it to work, but I haven't figured out how to get this to work with the bulk export CSV version...
CSV Version:
XLSX Version:
Note: The date columns are further down. I was able to get it working for 3 of the date columns, but when I apply it to the one at the top (DATE MODIFIED, a Date/Time field) the flow fails with the error indicated. However, when I look into the data it indicates that there are no blank/null values present.