Forum Discussion
Helper IVProper way to structure Date table
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
1 Reply
- Thejeswar
Super User
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!!
