SharePoint columns not updating

[Aitee]

I am updating my SharePoint from excel using Power Automate. 

I have a total of 11 columns but only 8 columns update while three (3) are blank though having data in the excel file. 

few things I have tried: 

1. change data type from Number to single line and to Multiple lines. 

2. Clearing the browser cache 

however issues persist. 

I need help !!! 

[wskinnermctc]

First thing I would try is deleting the flow actions and recreating the steps. Sometimes if you change SP List column information, it doesn't get recognized by Power Automate if it already has a link to the list. You kind of have to refresh the connector.

Can you show a screenshot of what the Excel file looks like. Primarily want to see the Excel column names.

Can you provide a screenshot of your power automate flow. And have a screenshot of the step that is Updating Item or Creating Items so that we can see the fields dynamic content being added from Excel.

[Aitee]

thank you @wskinnermctc 

I am sharing excel screen shots first: 

here is the flow below: 

Flow runs successfully without errors but those 3 columns DO NOT get updated. 

Thanks 

Aitee 

[wskinnermctc]

Excellent thank you for showing, and good job avoiding that second apply to each on the SharePoint Get Items by using first() on the ID.

Now I need one more thing.

The expressions being used on the columns that are not being filled. I see you have some if() functions, but need to see the remaining of it to check the issue.

You can copy and paste the expressions directly into a reply so I can see exactly how they are written. Otherwise you would have to hover over each one and take a screenshot showing the full expression.

[Aitee]

Thank you so much @wskinnermctc 

here it is as a screen shot (culprits highlighted) 

and then as text for easy edit 

if(empty(items('Publish')?['ProductLoss']),null,int(items('Publish')?['ProductLoss']))

if(empty(items('Publish')?['TimeLoss']),null,int(items('Publish')?['TimeLoss']))

let me quickly highlight that everything worked 3 weeks ago. But suddenly noticed this a few days back 

[wskinnermctc · Accepted solution]

Interesting, now we have a mystery.

Functionally everything looks good, and you know the small stuff like naming columns correctly which helps a lot. The expressions look fine, especially if they work for all of the other columns. So really all to do is start troubleshooting to see what is happening to the data.

You can go through these things in order to try and find the precise issue.

• Are any of the columns hidden in the Excel file? I see they aren't now, but was someone using the file table  hid the columns? That really shouldn't be an issue, but just something to double check while troubleshooting. 

• If you run the flow without the expressions does it work? Like just put the dynamic content into the field without checking for empty and run the flow. See if the values appear in the SharePoint list.

• If you run the flow (with or without expressions) and look at the outputs of the Create/Update action, does it show values in the Update/Create item for those rows? Basically trying to see if Power Automate does have values, but then SharePoint is not accepting them. Or if Power Automate is not even getting the values in the field in the first place.

• Would having the number 0 zero in the column be a problem? If you had a number type column, I think zero would be preferred anyways. The coalesce() will take the first non-null value. So if you use coalesce and the Excel field is empty, it will use the string(0), which will then be turned into an integer zero. Instead of using if(empty( you could try the expression below.

int(coalesce(items('Publish')?['ProductLoss'],string('0'))

• I know it is painful, but can you delete the Update/Create item actions; then re-add them? Or honestly I would delete the whole flow and re-create it. This would make sure all of the connectors are completely refreshed Excel and SharePoint. But do not use the copy/clipboard, you would have to literally add new actions and re create them. If you use the copy actions, it could duplicate the problem or cause another issue.

Can you check through some of those bullet points and see if anything changes? Let me know if you see something or anything changes.

(The final test is delete the SP List columns that are causing problems and try again with new columns. If that doesn't work delete everything, SharePoint List and Flow. The file wouldn't necessarily have to be deleted.

I have seen issues where the SharePoint columns are not being recognized by PA and new columns have to be created, and if that doesn't work a new SP list has to be created. This sometimes happens if there are changes being made the SP List and PA kind of bugs and doesn't recognize it. It is not common, but it is something that has happened to me and I've seen others with the issue and that was the solution.)

[Aitee]

Thanks @wskinnermctc 

Apologies our time zone difference could make me finish this discussion on Friday. 

I will try out these steps now and revert to you over the next hour or so. 

[Aitee · Accepted solution]

Hi @wskinnermctc 

• "If you run the flow without the expressions does it work? Like just put the dynamic content into the field without checking for empty and run the flow. See if the values appear in the SharePoint list"

First trying point you suggested above - worked for 2 columns and then writing an expression for the third worked. 

What I finally did was delete the create/update items actions and re-created them and I've got all columns updating now. 

Thank you so much 

[Aitee]

one more thing @wskinnermctc 

My excel sheet for these two columns are:

when I import into SharePoint it comes this way:

what have I tried?

1. importing without expression 9 same result as above)

2. used expression - if(empty(item('Publish')['RTF Start']),null,addDays('1899-12-30',int(item('Publish')['RTF Start']),'yyyy-MM-dd')). 

Returns error 

Any ideas to tweak the expression ? 

[wskinnermctc]

What type of SharePoint List columns are "RTF Start" and "RTF End" in the SharePoint List settings? Are they Single Line Text Type or DateTime?

In the Excel file for the "RTF Start" and "RTF End" what type of columns are they and what value is in the field. Is it a datetime type column and it has a date like 12/11/2023 12:19 PM but then the view format of the column is only showing the 12:19? 

Or does it just contain the 12:19?

Basically I want to know what value is being pulled into PowerAutomate from the Excel file. 

If you put a blank Compose step into your flow and added the Excel dynamic content of RTF Start, what does the value look like?

What Excel date should be combined with RTF Start? Is it the "Production Date", "Sales Date", "Time"?

I need to know how Power Automate is reading the Excel data as well as the desired output to the SharePoint list column.

[Aitee]

Thanks @wskinnermctc 

"RTF Start" and "RTF End" are time production "start" and "End" time per batch, my goal is to represent them just as it is in excel. (so in SharePoint in represent as Date/time)

In excel it is just 12:19 - no date attached to it 

I was hoping I could extract same way to SharePoint (time only).

Extracting directly without an expression in the flow returns samples like 12/30/1899 12:19

"RTF Start" and "RTF Stop" are manual time entries to show start and end times for production batches,  they are not calculated or referenced they are absolute values from our SAP system to signify when each batch of production starts and ends.

[wskinnermctc · Accepted solution]

You are not able to have Time Only in a SharePoint date/time type column. A SharePoint date/time type column can have Date Only or Date & Time, but it cannot have Time Only as a value.

A SharePoint date/time type column can't accept a Time Only value nor can it display Time Only of a full date-time value.

The only way to have a Time Only value in a SharePoint list is to use a Single Line Text type column. And then you put the "hh:mm" into the text field.

(It is possible to have a second Calculated column which displays Time Only, but it must reference a separate date/time type column which has a full date/time value. See example: Time Only in SharePoint List. But I don't think this is what you can do because you only have time value and not a date value. And using calculated columns usually leads to problems later.)

The only thing I can see that you could do would be to have two separate Single Line Text column types in the SharePoint list and then put the formatted time value into the field.

Since Power Automate is reading the Excel value "12:19" as date time and applying a date of 12/30/1899, you need to format this value with an expression to only show the time, and then put that into the single line text type columns.

To format the value into time only would use an expression like:

formatDateTime(items('Publish')?['RTFStart'],'HH:mm')

[Aitee]

Thanks @wskinnermctc 

This worked perfectly