DEV Community

thebernardlim
thebernardlim

Posted on

3

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.

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay