I’m needing a flow to complete these actions:
- List all rows from an excel file
- Put the values stored in each of the Excel rows and put them in a SharePoint List
- If a row is already in the SP list, then it should only update it
- If a row is not already in the SP list, then it should create a new row in the SP list
The issue:
Several of the columns in the Excel file contain dates. The columns are formatted as dates in Excel and in the SharePoint List. The dates are not always a required field, so some of the rows are blank in the date columns.
During an initial run of the flow, I received this error:
I then changed the DateTime Format under the List rows present in table card from Serial Number to ISO 8601.
This made the needed format change in the outputs, but it then threw a new error:
It's registering the null value for the some columns but for others it’s only registering “ ”.
I did try going into the Excel file and replacing all blank values with “null” to see if that would force it to register it as null and read the value, but it did not change the flow. I also made sure all dates are formatted the same, as short dates, in Excel.
I also noticed all the dates are reading differently, and I’m not sure why. For example, some are reading in the longer ISO8601 format SP uses, but others are reading as just the shorter, non- ISO8601 format. I don’t know that this is actually an issue (I don’t need the times for my dates), just an oddity.
I saw a couple similar situations online where a compose action was used to store and then convert each of the date values+plus tell power automate to account for the nulls, and I suspect it would work for this one if I did it for each one of the date columns. I ~think~ it might look something like this:
Template
if(equals(outputs('Compose'),' '),null,outputs('Compose'))
Modified version for my needs
if(empty(outputs('Compose'),' ',formatDateTime(outputs('Compose'),'MM-dd-yyyy'))
In other words, it should say:
Store this date value here. If it’s empty, then leave it blank. Otherwise, format it like ‘MM-dd-yyyy’.
There are probably other condition expressions that could be used too, like this one. The issue I'm running into with this option is that I don't actually need to check to see if a row is blank, because I don't care if it's blank or not. My flow needs to run the same either way.
That’s where it gets muddled for me and I can’t find an exact use case. I’m unsure of where the expression goes, the order it needs to be in, and how it needs to interact with the apply to each at the end of the flow. I’m also unsure of how it insert it into the step that creates and updates the SharePoint list where you have to fill in the equivalent values for the excel and SP columns.
Also, would it make more sense to have a separate child flow for making these fixes that then inserts into this flow, so it doesn’t become too convoluted?
Resources used:
Solved: Re: Date field throwing error if blank - Power Aut... - Power Platform Community (microsoft.com) https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate#:~:text=The%20Date%20and%20Time%20values%20from%20excel%20are,and%20change%20the%20datatype%20to%20general%20text%20format.
