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 manojk_pbi , Thank you for reaching out to the Microsoft Community Forum.
When you have a single column that contains both text and date values, Power BI's default behavior is to automatically interpret the column type as Date if it detects mostly dates, which causes the text entries to be formatted incorrectly or turned into blanks. To keep the data exactly as you have it, such as Combined with G5, NA and date strings like 2023-10-18, you need to explicitly set the column’s data type to Text during the data import or in Power Query. This stops Power BI from applying its usual date formatting and preserves both text and dates in their original format in a table visual.
This approach ensures that your mixed content appears unchanged in your report’s table, maintaining the format you expect. Without this adjustment, Power BI will try to convert values it recognizes as dates, which leads to unintended formatting differences or misrepresentation of text entries. While you cannot have multiple data types in one column in Power BI, treating the whole column as text is the most straightforward and effective solution for your case.
Use custom format strings in Power BI Desktop - Power BI | Microsoft Learn
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]
)