Forum Discussion
How to format date when mixed data in the same column
Hi manojk_pbi , Thank you for reaching out to the Microsoft Community Forum.
Explicitly treat all values as pure text with no implicit conversion to date types at any step. In Power Query, avoid any Change Type to Date and use Using Locale if needed to control parsing when converting dates. Additionally, double check the model and visual formatting settings, the column’s data type should remain Text and date formatting options should not be applied in the visualization pane.
If you want to display dates exactly as text in a specific format in the report, consider creating a calculated column using a DAX formula with the FORMAT function to enforce a uniform text representation of dates, while keeping other text entries as is.
Example:
Formatted Column =
IF(
ISDATE('Table'[OriginalColumn]),
FORMAT('Table'[OriginalColumn], "yyyy-MM-dd"),
'Table'[OriginalColumn]
)
hi v-hashadapu , data for this column is arked as Text while importing but still i see the same result
Hi manojk_pbi , Thank you for reaching out to the Microsoft Community Forum.
Explicitly treat all values as pure text with no implicit conversion to date types at any step. In Power Query, avoid any Change Type to Date and use Using Locale if needed to control parsing when converting dates. Additionally, double check the model and visual formatting settings, the column’s data type should remain Text and date formatting options should not be applied in the visualization pane.
If you want to display dates exactly as text in a specific format in the report, consider creating a calculated column using a DAX formula with the FORMAT function to enforce a uniform text representation of dates, while keeping other text entries as is.
Example:
Formatted Column =
IF(
ISDATE('Table'[OriginalColumn]),
FORMAT('Table'[OriginalColumn], "yyyy-MM-dd"),
'Table'[OriginalColumn]
)