Power Query sets dates back by 1 day when converting from datetime to date

[rywall]

I have multiple data sources for my report, some of which have datetime fields and others have date fields. I don't care about time, so I want to use date for all fields. However, when I use the "Date Only" function on a datetime column, it sets all the dates back by one day. For example, if one row had a value of 7/16/2019 2:31:07 PM, the result of the "Date Only" function will be 7/15/2019.

I had a really hard time believing that such a serious and fundamental bug exists, but I'm certain this isn't user error and this can't be the intended design.  I've tested this thoroughly and the result is consistent (regardless of the data source or the values). I've come up with a bunch of different workarounds for this, but they are all extremely aggravating. 

Many of these workarounds are also inconsistent. At one point, the strategy I was using was to change the datetime field to type text, extract the first 10 characters, then convert to type date. This worked perfectly until yesterday, but now it doesn't and Power BI gives an error. 

Please fix.

[v-qiuyu-msft]

Hi @rywall, 

Please go to Query Editor, select the column which contains datetime values, then change its data type to Date to see if it returns desired date values. 

Best Regards,
Qiuyun Yu 

[rywall]

Hey @v-qiuyu-msft Qiuyun,

Thanks for the response. This method yields the same result (it sets the date back by one day).

-Ryan

[v-qiuyu-msft]

Hi @rywall, 

May I know your Power BI desktop and machine regional settings? 

Best Regards,
Qiuyun Yu