Forum Discussion
Using Power Automate button in reports - Date Format error
Hi,
Wondering if someone can help me with this...
I have added a power automate button to my report, and set up a flow to update a row in an excel table. All is working well, but the date format is coming through as "YYYY-MM-ddT21:00:00.000Z", instead of, "dd/MM/yyyy".
Instead of the dynamic content, I tried using the expression: formatDateTime(item()?['Table[Column]'], 'dd/mm/yyyy') but I then got an error relating to null values (possibly some empty rows in data).
I tried: "if(empty(item()?['TableName[Column]']),'null',formatDateTime(item()?['TableName[Column]'], 'dd/MM/yyyy'))" - but all the dates then came out as null (so presuming there is an error in this expression?!!).
Any help is greatly appreciated I am not very familiar with Power Automate.
Thanks
Try putting the action in a "condition" step, where the condition is IsBlank(your field here), then for the equals enter false (just the word false on its own, lowercase). That way it will only do the action if it's not blank.
If IsBlank doesn't do it, you can also try IsEmpty(field name) or just put the field in the condition and check if it is equal to null. Depending on how blank your blanks are any of those could work. 🙂
4 Replies
- lbendlin
Super User
Don't try to fight that. Power Automate is by default using ISO8601 datetime formats. So the data is coming across in the correct format.
Thanks for replying!
I do get that this is the correct format for the data... but our accounts department doesn't want to work with the date in ISO8601 format. They simply need a short date (uk so dd/MM/yy), when an excel table is updated from a powerbi report using the flow button. Can you suggest an expression that will allow for formatting in this way, taking into account the whole "null" issue. I have checked the report data, and there are no blank/null rows that I can see.- Flow ran fine with ISO8601 format.
- Expression: formatDateTime(item()?['Append2[Start Date]'],'dd/mm/yy') - resulted in Flow Failed, a "One or more fields provided is of type 'Null', a different type is expected" error.
- Expression: if(empty(item()?['Append2[Start Date]']),null,formatDateTime(item()?['Append2[Start Date]'],'dd/MM/yyyy')) - flow successful but resulted in every Start Date field being populated by "null" in excel.
Any help greatly appreciated!! 😊
- christinepayton
Most Valuable Professional
Try putting the action in a "condition" step, where the condition is IsBlank(your field here), then for the equals enter false (just the word false on its own, lowercase). That way it will only do the action if it's not blank.
If IsBlank doesn't do it, you can also try IsEmpty(field name) or just put the field in the condition and check if it is equal to null. Depending on how blank your blanks are any of those could work. 🙂
