Date and time field in MS Lists: displaying as 'today' or 'yesterday' and causing reporting errors.

Question

Date and time field in MS Lists: displaying as 'today' or 'yesterday' and causing reporting errors.

Glen Campey 20

I'm working with a Microsoft List and using PowerBI to build data reports.

We have a date and time field in the List, which was set to 'today's date' as default. Working with Copilot, we've identified that new records are not pulling through to PowerBI because the date field is not being populated with today's date, but the word 'today', which throws up an error in PowerBI and excludes the record.

I tried removing the default value for the date, but then selecting or entering today's date still only displays the word 'today'. It's similar when I input yesterday's date, except it gives me the word 'yesterday', where PowerBI is looking for a date.

This seems a bit of a weird decision for a field that has the format explicitly set to date and time - could we sort that out?

0 comments

Answer accepted by question author

Liora D 18,915 Microsoft External Staff Moderator

Hi @Glen Campey,

Thank you for the update, and I'm glad you've found a workaround by using the Created field.

One helpful observation is that the Created field continues to work correctly in the same List and Power BI reporting process, even when Microsoft Lists displays values such as "2 hours ago", "Today", or "Yesterday". This suggests that Power BI is still able to consume date/time values from the List successfully.

Based on the information available so far, I don't think we have enough evidence to conclude that Microsoft Lists is generally storing text values such as "Today" or "Yesterday" in place of actual date/time values. What we can say is that the behavior appears to be affecting the specific custom Date & Time column you created, while the Created field continues to function correctly.

If you would like to investigate further, it would be helpful to review the column configuration, the data returned from the List connector, and the values visible in Power Query. That would help determine whether the issue originates from the custom column itself, its configuration, or the way the data is being interpreted downstream.

In the meantime, the fact that the Created field works correctly is encouraging, as it suggests the issue may be limited to a specific column rather than affecting all Date & Time fields in Microsoft Lists.

Thank you again for sharing your observations and the workaround you discovered. I wish you the very best with your reporting project, and I hope everything continues to run smoothly going forward.

Warm regards,

0 comments

2 additional answers

Your answer

Answer 1

Glen Campey 20

Hello - it is a 'display-format behavior in Microsoft Lists' - I've switched to using the 'Created' field in the reporting, and they are all working fine (even when Lists displays the date as '2 hours ago' or similar).

The field we had created with a Date and Time format was using the text 'Today' instead of today's date, which was not just a UI choice as it is with the default Created field.

I now have a work around for this particular problem, but I wouldn't feel confident in the integrity of other date and time fields in Lists at this point.

0 comments

Answer 2

Dear @Glen Campey,

I hope you’re having a good day.

Based on your description, this issue appears to involve the interaction between Microsoft Lists and Power BI, particularly how the date values are being interpreted during data import or refresh.

While the source data originates in Microsoft Lists, the actual problem you are encountering is that Power BI is excluding records because it appears to be receiving or interpreting values such as "Today" or "Yesterday" rather than the expected date format.

Because the investigation will likely require reviewing:

How Power BI is connecting to the Microsoft List.
How the date column is being interpreted in Power Query.
Any transformation steps applied during data import.
The specific error generated during dataset refresh.

I would recommend posting this question in the Power BI forums, where Power BI specialists can help analyze the dataset, query steps, and refresh behavior in greater detail: Power BI forums - Microsoft Fabric Community

When posting there, it would be helpful to include:

A screenshot of the Microsoft Lists column settings showing the field type.
A screenshot of the values displayed as "Today" or "Yesterday".
The exact Power BI error message.
Whether Power BI is connected through the SharePoint Online List connector, OData feed, or another method.
A screenshot of the relevant Power Query steps (especially any Changed Type or date conversion steps).

Providing those details will help the Power BI community determine whether this is:

A display-format behavior in Microsoft Lists.
A data conversion issue in Power Query.
A connector-specific issue.
Or a problem with how the date field is being interpreted during refresh.

I hope this points you to the right community for further investigation, and I hope you're able to identify the cause and get your reports working correctly again.

Warm regards,

If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.