Power BI Dataset date column shows up as a datetime column in the report pbix

[powerquest1234]

My Power BI Dataset has a column "Rejection Date" that is supposed to be a date data type. However, when I open a new .pbix and connect to that dataset, the Rejection Date column shows as a datetime data type. (For reference, I keep my dataset in one pbix file and my reports in separate pbix files).

I have confirmed the following things: 

- In my pbix file that stores the dataset open power query and confirm that Rejection Date shows as a date column in power query. I even added a step at the end of the power query transformations where I transform Rejection Date into a date column, even though it was already a date data type.

- In my pbix file  report view, I click on the Rejection Date column. Under Column Tools, Rejection Date shows as a date data type column. For extra measure, I have sometimes gone in and changed it to a date data type *again*.

- I have double and triple-checked that I am publishing this dataset to the workspace.

However inevitably, when I open a new .pbix file and connect to this dataset, Rejection Date shows as a datetime data type. There is no time information in the field's values, just a short-format date, but the column tools view shows data type datetime. I have refreshed multiple times over the past few weeks, re-published the dataset multiple times (ensuring each time that Rejection Date is a date data type in the dataset .pbix file)  and this is still the case. 

Has anyone else experienced this? Could this cause problems? What should I do?

[ichavarria]

Hi @powerquest1234,

It's possible that the issue is related to the way Power BI Desktop and the dataset are interacting. Here are a few things you can try to resolve the issue:

1. Check the data type in the data source: Confirm that the data type of the Rejection Date column in the data source (e.g. Excel or CSV file) is a date type, not a datetime type.

2. Clear the cache: Clear the cache for Power BI Desktop and try connecting to the dataset again. To do this, go to File > Options and settings > Options > Data Load, and select "Clear Cache."

3. Check the data type in the dataset: Make sure that the Rejection Date column is a date type in the dataset. To do this, open the dataset .pbix file, go to the "Transform Data" screen in Power Query, select the Rejection Date column, and check that the data type is set to "Date."

4. Rebuild the dataset: If the above steps don't work, try rebuilding the dataset from scratch. Create a new .pbix file, connect to the data source, and recreate the dataset and any transformations you've applied.

5. Contact Microsoft support: If none of the above steps work, you can contact Microsoft support for further assistance.

It's important to ensure that the data type of the Rejection Date column is correct, as using the wrong data type can cause issues with calculations and visualizations in your reports.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[powerquest1234]

Thank you so much for your suggestions.

"Check the data type in the data source: Confirm that the data type of the Rejection Date column in the data source (e.g. Excel or CSV file) is a date type, not a datetime type."
"Check the data type in the dataset: Make sure that the Rejection Date column is a date type in the dataset. To do this, open the dataset .pbix file, go to the "Transform Data" screen in Power Query, select the Rejection Date column, and check that the data type is set to "Date."

The datasource is an oracle table which stores the Rejection Date in two fields: a "Quality Control Rejected Date" datetime field and a "Submitter Rejected Date" datetime field. Each rejected record would either have a QC Rejected Date or a Submitter Rejected Date. For the purposes of my report, in a dataflow native query I combine the QC Rejected Date and Submitted Rejected Date into one Unified Rejection Date datetime column (because my user wants to look at records by rejection date with the dates in one column). In the dataflow power query screen, I then split the Unified Rejection Date column into a Unified Rejection Date (type date) and Unified REjection Time (type time) column. In the dataset power query, the Unified Rejection Date field enters as a date. However, because this has been so frustrating, I also added a step to the power query that types the Unified Rejection Date as a date field. When I look at the dataset pbix file, it shows that the Unified Rejection Date field is a date field. It is only in the report (connected to the dataset pbix file with live connection) that the field is showing as datetime.

"Clear the cache: Clear the cache for Power BI Desktop and try connecting to the dataset again. To do this, go to File > Options and settings > Options > Data Load, and select "Clear Cache.""

In the report file:

Global section: I was able to clear the cache, then I tried to refresh the data. It's still showing as a datetime.

When I go to this under the Local selection, I'm showing an error message that says "Data loading options aren't available because this file uses a live connection to another model."

I opened a new blank pbix file and connected to the dataset after I cleared the cache in the global setting. The column is still showing as datetime.

I will try the other options and report back. It's so strange that the field is showing as a date data type in the dataset, but not in the report.