Create CSV Table formatDateTime Null

[PowerAutomater]

I am using the header area of the Create CSV Table action to change the date format of all the date columns/fields:

formatDateTime(item()?['Query1[Due Date]'], 'd/M/yyyy')

Unfortunately I am getting an error that the value is of type Null. Here is the full error:

The execution of template action 'Create_CSV_table' failed. The column values could not be evaluated: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'.

This seems to happen only for columns where some of the rows are blank, as those without any blanks run fine. The trouble is that the data source being used is going to have blanks in certain columns with dates, there's no way around it.

There's 2 possible ways I can think of solving this (but of course welcome to all suggestions); either I add some kind of conditional that only tries to format the date if there isn't a blank/null, or perhaps there's something I can do to the data in PowerBI? The only trouble is, as the data source will be updated periodically overtime, the change needs to be automated as manually doing it each time the data source needs updating will take a significant amount of time.

How can I resolve this?

[DataNinja777 · Accepted solution]

Hi @PowerAutomater ,

To handle the issue with null values in the Create CSV Table action when using the formatDateTime() function, you can modify your expression to include a conditional check. The error you're encountering happens because the formatDateTime() function expects a valid string as input, but it fails when the date column contains null or blank values. Since your data source will have blanks in certain date fields, you need to account for this scenario to ensure your flow doesn't break.

One solution is to adjust your expression in Power Automate to check if the value is null before attempting to format it. You can achieve this by using an if statement with the empty() function. The updated expression would look like this:
if(empty(item()?['Query1[Due Date]']), '', formatDateTime(item()?['Query1[Due Date]'], 'd/M/yyyy')).
This expression first checks if the date field is empty. If it is, it returns an empty string, ensuring that the flow continues without errors. If the field has a value, it proceeds to format the date in the specified format.

If you'd rather handle this issue in Power BI before the data reaches Power Automate, you can do so by transforming the date column in Power Query. One way to do this is to replace null values in the date column with an empty string or a placeholder date such as 01/01/1900. Alternatively, you can create a custom column in Power Query that checks for null values and applies the desired date format only if the value exists. The formula for such a custom column would be:
if [Due Date] = null then "" else Date.ToText([Due Date], "d/M/yyyy").
By transforming the data at the source, you ensure that the CSV export process runs smoothly without encountering null values in the date columns.

Another approach you might consider is using a default date for any blank fields instead of leaving them empty. For example, you can modify the expression to return a default value like 01/01/1900 if the date is missing:
if(empty(item()?['Query1[Due Date]']), '01/01/1900', formatDateTime(item()?['Query1[Due Date]'], 'd/M/yyyy')).
This ensures that your CSV file has consistent date entries, even if some fields were initially blank.

The best solution depends on where you want to handle the issue. If you want a fully automated solution that requires no manual intervention, modifying the expression in Power Automate is ideal. On the other hand, if you want to ensure clean data before exporting it, handling it in Power BI is a good option. Both approaches ensure that your flow processes smoothly and produces a CSV file without errors, even when some date fields are blank.

Best regards,