Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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
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.
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