Hello Power BI Community,
I'm facing a very strange issue when trying to mark a table as a Date Table in Power BI Desktop. I have created a calendar table in Power Query with minute-level granularity, but when I try to mark it as a Date Table in the Model view, I get the following error message:
"The date column must have unique values." (or "La columna de fecha debe tener valores únicos." in Spanish)
However, I have performed several steps in Power Query to ensure the uniqueness of the timestamp column, and a direct check in the Power BI data model using DAX confirms that the column does have unique values.
Here are the details:
Goal: Create a calendar table in Power Query with minute-level granularity for a specific date range and mark it as the Date Table in the Power BI model.
Date Range: From 2024-10-01 to 2025-05-31.
Steps taken in Power Query:
- Generated a list of dates for the range.
- For each date, generated a list of minute durations (0 to 1439).
- Combined the date and the minute duration to create a Timestamp column.
- Ensured the Timestamp column has the DateTime data type.
- Added a step to explicitly truncate the Timestamp to minute precision (setting seconds and milliseconds to zero).
- Added a step to explicitly remove duplicates based on the Timestamp column.
The Power Query preview shows the Timestamp column with the correct DateTime format, and the "Remove Duplicates" step does not appear to remove any rows, suggesting uniqueness in the PQ preview.
M Code for the Calendar Table:
let
// Define the date range
StartDate = #date(2024, 10, 1),
EndDate = #date(2025, 5, 31),
// Generate list of dates
NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
ListOfDates = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
TableOfDates = Table.FromList(ListOfDates, Splitter.SplitByNothing(), {"Date"}),
// Generate list of minutes for each day
ListOfMinutesInDay = List.Numbers(0, 24 * 60),
TableOfMinutes = Table.FromList(ListOfMinutesInDay, Splitter.SplitByNothing(), {"MinuteOfDay"}),
// Cross join dates and minutes
AddedMinuteTable = Table.AddColumn(TableOfDates, "Minutes", each TableOfMinutes),
ExpandedMinuteTable = Table.ExpandTableColumn(AddedMinuteTable, "Minutes", {"MinuteOfDay"}),
// Create Timestamp
CombinedDateTime = Table.AddColumn(ExpandedMinuteTable, "Timestamp", each DateTime.From([Date]) + #duration(0, 0, [MinuteOfDay], 0)),
// Remove temporary column
RemovedMinuteOfDayColumn = Table.RemoveColumns(CombinedDateTime, {"MinuteOfDay"}),
// Ensure Timestamp is DateTime type (explicitly)
ChangedTimestampType = Table.TransformColumnTypes(RemovedMinuteOfDayColumn,{{"Timestamp", type datetime}}),
// Truncate Timestamp to minute precision
TruncatedToMinute = Table.TransformColumnTypes(ChangedTimestampType, {"Timestamp", each DateTime.From(Date.From(_) & Time.From(_ - #duration(0,0,0,Date.Second(_)) - #duration(0,0,0,0,DateTime.Millisecond(_))), type datetime}),
// Extract date and time components
AddedYear = Table.AddColumn(TruncatedToMinute, "Año", each Date.Year([Timestamp]), Int64.Type),
AddedMonth = Table.AddColumn(AddedYear, "Mes", each Date.Month([Timestamp]), Int64.Type),
AddedMonthNameFull = Table.AddColumn(AddedMonth, "Nombre del Mes", each Date.MonthName([Timestamp]), type text),
AddedMonthNameShort = Table.AddColumn(AddedMonthNameFull, "Nombre del Mes Abreviado", each Text.Start([Nombre del Mes], 3) & "."),
AddedDay = Table.AddColumn(AddedMonthNameShort, "Día", each Date.Day([Timestamp]), Int64.Type),
AddedDayOfWeekNumber = Table.AddColumn(AddedDay, "Número del Día de la Semana", each Date.DayOfWeek([Timestamp]), Int64.Type),
AddedDayOfWeekNameFull = Table.AddColumn(AddedDayOfWeekNumber, "Nombre del Día", each Date.DayOfWeekName([Timestamp]), type text),
AddedDayOfWeekNameShort = Table.AddColumn(AddedDayOfWeekNameFull, "Nombre del Día Abreviado", each Text.Start([Nombre del Día], 3) & "."),
AddedHour = Table.AddColumn(AddedDayOfWeekNameShort, "Hora", each Time.Hour([Timestamp]), Int64.Type),
AddedMinute = Table.AddColumn(AddedHour, "Minuto", each Time.Minute([Timestamp]), Int64.Type),
// Reorder columns (optional)
ReorderedColumns = Table.ReorderColumns(AddedMinute, {"Timestamp", "Año", "Mes", "Nombre del Mes", "Nombre del Mes Abreviado", "Día", "Número del Día de la Semana", "Nombre del Día", "Nombre del Día Abreviado", "Hora", "Minuto"}),
// Explicitly remove duplicates after truncating and reordering
#"Removed Duplicates" = Table.Distinct(ReorderedColumns, {"Timestamp"})
in
#"Removed Duplicates"
Diagnostic Check in Power BI Data Model (DAX):
After applying the Power Query changes to the Power BI model, I created a calculated column in the table using the following DAX formula to verify the uniqueness of the Timestamp column:
Duplicate Count =
COUNTROWS(
FILTER(
'YourCalendarTableName', // Replace with your actual table name
'YourCalendarTableName'[Timestamp] = EARLIER('YourCalendarTableName'[Timestamp])
)
)
The result of this calculated column for every single row is 1. This definitively confirms that the Timestamp column in the Power BI data model contains unique values.
Conclusion:
Based on the DAX verification, the Timestamp column is indeed unique in the data model. The error message received when attempting to mark the table as a Date Table appears to be a false positive triggered by the Power BI Desktop validation process itself.
Environment Details:
- Power BI Desktop Version: [ 2.141.1754.0 64-bit (march de 2025)]
- Operating System: [Windows 11 Home, Version 22H2, 26100.3775]
Request for Help:
Has anyone encountered this issue before where the "Mark as date table" validation incorrectly reports non-unique values despite DAX confirming uniqueness?
Are there any known workarounds, specific requirements for this validation beyond simple uniqueness (e.g., related to precision handling in the validation code itself), or steps I can take to diagnose why this specific validation is failing in my environment?
Any help or insight into this problem would be greatly appreciated.
Thank you!
