Stop Powerbi converting date to date time and taking off 1 hour

[RobG123]

Hi,

I have connected data from a sharepoint list into PowerBI. The dates in the sharepoint list are in date format, but when PowerBI ingests it converts them to date time. It also takes off 1 hour in some cases, I think this is related to daylight savings as it does this for dates from late March to end October. I think it might be trying to remove daylight saving from the dates in that period.

Does anyone know how I can stop PowerBI doing this? It means that some dates are different between the list and powerBI dashboard. 

Thanks
Rob

[artemus]

Oh, its already a datetime type. You could try adding a timezone to the datetime using DateTime.AddZone([Date], 0, 0) which would convert it to UTC.

[RobG123]

Thanks, but this leaves times unaffacted. E.g. 07/10/2020 23:00, it converts to the same date time. In the source data in sharepoint this appears as 08/10/2020.

Just to point out I have many date fields that PowerBI is making this 'auto-correction' to, so ideally I would prevent it from doing that. Rather than transofrming every column individually in PowerBI. 

[artemus]

You can manually convert the value to datetime (which should avoid this issue) with:

[DateColumn] & #time(0, 0, 0)

[RobG123]

Thanks for your response.

The code doesn't work because the field is already in Date Time format. PowerBI ingests as date time.

I can extract the date only from the date time column, but this gives me the day before the actual date as entered in sharepoint for March to October dates.