Proper way to structure Date table

[ilcaa72]

i have a 'fact table' that has dates...

1. within PQ, i right click the date column and selected, 'make query', then removed duplicates, then converted to table, and formated as 'Date'. so I have individual Dates in seperate table.

2. i then used the Date button functionality in PQ toolbar to automatically create Year, Month, Month Name, Quarter, etc columns, for Time Intelligence

when i load the table into PBI, it summarizes any numeric column in my 'Date table'.  So my question is... do I need to change the extra column types to dates as well?  Can i label this a 'Date Table'?  i just dont want any issues when i start buling my reports and using Time Intelligence functions. enclosed in screenshot

what r the best practices when using the Date button in PQ to add columns for time intelligence ?  thanks

[Thejeswar]

Hi @ilcaa72,

Basically the Numeric Columns that you create using the Date option in Power Query are given the data type as Whole Number or Decimal Number by the system

Also having this as a number as you said will summarize the column whenever you use it.

But by Functionality as well as logically, columns like Month Number, Year, Quarter Number, Week of Year etc. would need require them to be aggregated.

So it is better that you keep them as attributes, just like Month Name and only your Date column in Date Data Type as it is your key. i.e,. You can keep their data types also as Text and this would not create any issue in future

Hope this answered your question!!