Formatting a Date Column with mixed data type

[judinwa]

Hi team, I am new to PowerBI and excited to join this great community.

I am working on a project which has some date columns formatted weirdly see sample below.

I have tried to format the column to a date format but keep getting error for some of the values in the column (see screenshot below)

I can format the column in excel before importing it to power query but I was wondering if there is a way to format it directly in power query.

I will like to know if it is possible to format the column directly in power query and suggestions on how to do that, thank you

[ronrsnfld · Accepted solution]

Given your displayed data,

you merely need to specify the culture as one that has a format of dd-mm-yyyy. It would appear that your computer's default date format is MDY and the mismatched values are in DMY format.

eg:

let

//replace Source line with your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"issue_date", type date}},"en-150")
in
    #"Changed Type"

"en-150" is the culture code for Europe, although others will work as well.

Note that the dates above are in MDY format which relates to my Windows Regional Settings.

[judinwa]

Hi @ronrsnfld , thank you so much, your solution worked 😁

[Ahmedx]

pls try

[lbendlin]

Format the column as integer first, and then as Date.  Choose "Add Step" rather than "replace"