How to format date when mixed data in the same column

[manojk_pbi]

Hello,

I have a column containing some texts and date values. how do i present this in table chart ? I am expecting the format maintained as it is 

On importing data to PowerBi, I am not getting date in required format. Below is the same data

Combined with G5
Combined with G5
NA
NA
2023-10-18
Combined with G5
2024-05-01
2026-06-26
2026-06-26

I am getting like below

[v-hashadapu]

Hi @manojk_pbi , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @manojk_pbi , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[v-hashadapu]

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

[manojk_pbi]

hi @v-hashadapu , data for this column is arked as Text while importing but still i see the same result

[v-hashadapu · Accepted solution]

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]

)

FORMAT function (DAX) - DAX | Microsoft Learn

Data types in Power Query - Power Query | Microsoft Learn