How to make DATE_M field as date field.

[Anonymous]

Hi All

when i check PQ , i notice below 2 filed are date field. include DATE_M field.

My table DATE_M field not indicate as date field. see the red box.

[PhilipTreacy]

hi @Anonymous 

Can you link to your PBIX?  By email?

Regards

Phil

[Anonymous]

Hi Phil

Can i have your email address ?

Paul

[Anonymous · Accepted solution]

Hi @Anonymous 

I think you may build a relationship between DATE_M column and other Date column in other tables.

If you build a relationship between date type column, you may be unable to use date hierachy in this column.

In my Sample:

Result:

You can try to remove relationship between Date_M and other Date columns.

If you need this relationship, you can duplicate Date_M in Power Query Editor named Date_M2, then you can use date hierachy in Date_M2.

Or you can add some calculated / custom column like Year, Quarter,Month,Day, then you can build date hierachy by yourself.

Best Regards,

Rico Zhou

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

[Anonymous]

Hi Rico

Wow you are good , you can visulise my issue . I will try it out later , many thank.

Paul

[PhilipTreacy]

Hi @Anonymous 

Amit's screenshot highlighted the formatting area on the menu, not the Data Type area.  I wanted to make sure you'd checked the correct section of menu.

Phil

[PhilipTreacy]

Hi @Anonymous 

You need to set the data type in the Data view/tab for the DATE_M column to Date.  As I showed in my last image

Regards

Phil

[Anonymous]

Hi Phil

Since i already set the DATE_M as Date Type. But beside the DATE_M field , does not have date icon , why ?

Paul

[PhilipTreacy]

Hi @Anonymous 

It's the Data Type you need to check is set to Date, not the Formatting

Regards

Phil

[Anonymous]

Hi All

How to make date icon appear at DATE_M field ?

[amitchandak]

@Anonymous , In options and setting, there is an option to clear cache, Try that.

If you still face that. And you are on Dec-2020, Then check of any know issue or log a new one :https://community.powerbi.com/t5/Issues/idb-p/Issues

[amitchandak]

@Anonymous , Check Data type in column tools. Once you click on column you should able to see the tab. It might have changed there. Change it to date , if it is text there.

[Anonymous]

Hi Amit

I have just check it is date format.  what else i need to do ?