Share
Report
32I am trying to use a flow to add items to a SharePoint list from an Excel worksheet. I have the following requirements:
1. Date columns are not clean. The column is formatted as a date but some cells have "?" marks, or are blank. I need to accept the cells that are dates and ignore the other data. The Date/Time Format in the List Rows step is set to "ISO 8601"; the column in the SharePoint list is Date/Time.
2. I want to change the data in one column, if it exists, to another value.
I do not want to change the Excel spreadsheet because this flow will need to be run several times. The spreadsheet is production data.
For each requirement, I think I need an expression to make it work.
For requirement 1, I am trying to use an "If" statement in the expression:
if(item()?['Column3']="Input One","Option1",if(item()?['Column3']="Input Two","Option2,item()?['Column3'])
A value of Option1 or Option2 should use a different value, otherwise it should keep the value in the cell.
Following numerous posts for this issue for requirement 2, numerous expressions have not returned joy:
formatDateTime(parseDateTime(item()?['Column5'], 'da', 'dd-MM-yyyy'), 'yyyy-MM-dd') passes the syntax checker but fails is I try wrap it in an "If" statement to ignore the "?"s and the empty cells.
if(empty(item()?['Column5']) or (item()?['Column5']="?"),null, formatDateTime(parseDateTime(item()?['Column5'], 'da', 'dd-MM-yyyy'), 'yyyy-MM-dd')) -- syntax error.
I've checked for parens and other syntax issues but have not located the problem.
Any insight/guidance would be appreciated.
Categories:
All responses (
Answers (
