Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MattTrollope
Frequent Visitor

Converting BST DateTime to UTC Date from Dataverse data import

Hi

I have a DateTime field that during BST is imported and stored as 23:00 on the previous day. When I remove the time from it (to enable searching specific days), it is then stored as one day earlier than it should be. Is there a straightforward solution to solve this issue?

 

Thank you for any assistance you can offer.

 

1 ACCEPTED SOLUTION
mark_endicott
Super User
Super User

@MattTrollope - I assume you're doing this in Power Query, in which case the logic below creates a TRUE/FALSE column in a table to define BST dates. Hopefully you can use this to implement appropriate logic to subtract an hour off the time where necessary:

 

 

isBST = Table.AddColumn(#"Changed Type", "isBST", each 
    ([date_value] >= Date.StartOfWeek(#date(Date.Year([date_value]),3,28), Day.Sunday))
    and 
    ([date_value] < Date.StartOfWeek(#date(Date.Year([date_value]),10,28), Day.Sunday))),

 

 

[date_value] is the name of the date column in your table. 

 

Starting point would be to wrap this entire statement (after each) in an IF and then implement the necessary logic to minus one hour.

 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

View solution in original post

2 REPLIES 2
mark_endicott
Super User
Super User

@MattTrollope - I assume you're doing this in Power Query, in which case the logic below creates a TRUE/FALSE column in a table to define BST dates. Hopefully you can use this to implement appropriate logic to subtract an hour off the time where necessary:

 

 

isBST = Table.AddColumn(#"Changed Type", "isBST", each 
    ([date_value] >= Date.StartOfWeek(#date(Date.Year([date_value]),3,28), Day.Sunday))
    and 
    ([date_value] < Date.StartOfWeek(#date(Date.Year([date_value]),10,28), Day.Sunday))),

 

 

[date_value] is the name of the date column in your table. 

 

Starting point would be to wrap this entire statement (after each) in an IF and then implement the necessary logic to minus one hour.

 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

Thank you for your response.

I was doing something very similar (although your formula for determining BST/UTC change dates is more elegant than mine, where I had hard-coded the March/September dates). I was hoping there might be a better way to approach the problem.

It works for many dates, but for some reason it returns nothing (no date) when I try to move the date from 30th June to 1st July (for all years). I'm aware this is a different issue, so this isn't the apprpriate place to take it further.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors