Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PowerBI_Chaos
Helper I
Helper I

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!

 

1 ACCEPTED SOLUTION
christinepayton
Super User
Super User

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

View solution in original post

5 REPLIES 5
christinepayton
Super User
Super User

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

 

 

PowerBI_Chaos_0-1702416200301.png

 

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

 

PowerBI_Chaos_0-1702485245755.png

CSV Version:

PowerBI_Chaos_2-1702485426295.png

 

XLSX Version:

PowerBI_Chaos_3-1702485561113.png

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.

aj1973
Community Champion
Community Champion

Hi @PowerBI_Chaos 

I think this should be formated in the flow you created in Power Automate.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.