Add Column - Date - Start of Day and End of Day return invalid data types date and datetimezone

[MarcelBeug]

In the Query Editor, if you select a date or datetimezone type column and choose Add Column - Date - Day - Start of Day or End of Day, the result gets an incompatible data type datetime, instead of date or datetimezone.

To make things worse: the results are still visible in the Query Editor, but the data won't load to the data model. (See also issue: http://community.powerbi.com/t5/Issues/Table-AddColumn-and-Table-DuplicateColumn-may-display-invalid...).

Example of created code (Start of Day for a date column):

= Table.AddColumn(SampleData, "StartOfDay", each Date.StartOfDay([Date1]), type datetime)

The cause of the issue is with the data type datetime that is supplied as parameter columnType with Table.AddColumn, while the actual data is date or datetimezone.

Likewise: for other "Start of..." and "End of..". options (Year, Month, Quarter, Week) on the Add Column menu, the generated code differs according to the datatype of the selected column.

My suggestion is to do the same with Start of Day and End of Day.

[v-haibl-msft]

@MarcelBeug

Please take a look at this document, Date.StartOfDay needs a datetime value as parameter and will always return a DateTime value for the start of the day.

So in this case, we should not use Date.StartOfDay for a date or a datetimezone column.

Best Regards,

Herbert

[MarcelBeug]

The linked document is imcorrect.

If the function shouldn't be used for dates and datetimezones, why is the standard option available for those data types?

Please take a look at this video.

[v-haibl-msft]

@MarcelBeug

Thanks for your video. I’ve reported it internally to Power BI Team and they’ve filed a bug to track this.

Best Regards,

Herbert

[v-haibl-msft]

@MarcelBeug

The fix would be included in the March release of PBI Desktop.

Best Regards,

Herbert

[v-haibl-msft]

@MarcelBeug

This issue should has been fixed now in Mar 2017 version of Power BI Desktop.

Best Regards,

Herbert

[MarcelBeug]

Confirmed. Thanks!