DEV Community

thebernardlim
thebernardlim

Posted on

Power Automate - How to solve missing Date/Time values when using Excel Forward Slash (/) in Column Header

Problem

There is a weird quirk when extracting values from Excel spreadsheets to SharePoint lists using Power Automate. This issue only happens when using forward slashes in column headers specifically for Date/Time cells

Here is my Excel Online spreadsheet. The Date/Time column is of Date Time format.
Excel

The values will be stored in a SharePoint list.

Here is how a typical flow for such will look like. Pay attention to the Date/Time column.

SP1

After running, there will be no errors. However, as seen below, there will be no values in the Date column in SharePoint list

SP2

If you click on Edit once more, it will show that the value of the Date/Time Excel column header has changed to Date~1Time

Converted column title


Solution

Use an expression instead for such a column. For my case, my column is labelled as Date/Time and the Apply to each control's name is "Apply to each", hence it will be:

items('Apply_to_each')?[concat('Date', '/', 'Time')]

Honestly, I am not sure why this is happening just for forward slashes. There are a couple of special characters that will cause issues for Date/Time columns as well such as @, # to name a few.

Top comments (0)