Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : eTugRFFVrL6japliEQUKw+
Power Platform Community / Forums / Power Automate - Building Flows / Excel data requires ex...
Power Automate - Building Flows
Answered

Excel data requires expressions

Like (0) ShareShare
ReportReport
Posted on 13 Mar 2025 12:42:57 by hcharrisbna 32
I 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:
Business Process Flows
  • hcharrisbna Profile Picture
    hcharrisbna 32 on 14 Mar 2025 at 14:03:31
    Excel data requires expressions
    You are absolutely correct. It was not needed. I included it because I was following numerous posts while seeking a solution.
     
    I will try to get a better understanding of data type conversions and utilizations.
     
    Thank you again.
     
    carter
  • Verified answer
    Building with Why Profile Picture
    Building with Why 193 on 14 Mar 2025 at 13:43:29
    Excel data requires expressions
    I did not touch the parseDateTime, but I am unclear as to why you need it.  You are already telling Excel to use ISO 8601 format.
    I would remove the parseDateTime altogether.
     
    If you need it for some reason I am not aware of
    Correct format is  
    parseDateTime('<timestamp>', '<format>', '<locale>')
    parseDateTime(item()?['Column7'], 'yyyy-MM-dd', 'en-us')
     
    In which case the formatdatetime statement is not needed.
     
  • hcharrisbna Profile Picture
    hcharrisbna 32 on 14 Mar 2025 at 13:13:48
    Excel data requires expressions
    Error, on 2nd issue:
     
    instruction:
     
    if(or(empty(item()?['Column7']), equals(item()?['Column7'], '?')), null, formatDateTime(parseDateTime(item()?['Column7'], 'en-us', 'en-us'), 'yyyy-MM-dd'))
     
    message:
     
    Unable to process template language expressions in action 'Create_item' inputs at line '0' and column '0': 'In function 'parseDateTime', the value provided for date time string '2016-04-18T00:00:00.000Z' was not valid. The datetime string must match ISO 8601 format.'.
     
    I'm trying to learn how to interpret the error messages. I believe the error is in the parseDateTime function. The content of the cell is "04/18/2016" formatted as number/date. Various parameters for the parseDateTime have been tested (e.g. 'da', 'dd-MM-yyyy', and the one above) but nothing seems to work.
     
    Thank you, Building With Why for your responses. 
  • hcharrisbna Profile Picture
    hcharrisbna 32 on 13 Mar 2025 at 14:39:52
    Excel data requires expressions
    Thank you for your reply. I believe that you are on the right track.
     
    For #1: Your suggested expression seemed right, but it does not pass the syntax check so I tried to simplify it so I would have something I could build on. 
     
    Eventually I discovered that the quotes should be single quotes instead of double quotes. 
     
    For #2: I'm still testing the suggested expression. Will simplify to get a clean check then try to enhance it. When I solve this one, I'll close this post.
     
    I appreciate your suggestions. Your guidance sent me to looking at it other ways.
     
    carter
     
     
  • Building with Why Profile Picture
    Building with Why 193 on 13 Mar 2025 at 13:10:53
    Excel data requires expressions
    For 1.
    Power Automate uses a different format.  try  if(equals(item()?['Column3'], "Input One"), "Option1", if(equals(item()?['Column3'], "Input Two"), "Option2,item()?['Column3'])
  • Building with Why Profile Picture
    Building with Why 193 on 13 Mar 2025 at 13:07:49
    Excel data requires expressions
    For Item 2:
     
    if(empty(item()?['Column5']) or (item()?['Column5']="?"),null, formatDateTime(parseDateTime(item()?['Column5'], 'da', 'dd-MM-yyyy'), 'yyyy-MM-dd'))
     
    you are missing equals before (item()?['Column5']="?")

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,431 Most Valuable Professional

Leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans