Forum Discussion
Formatting a Date Column with mixed data type
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
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.
4 Replies
Format the column as integer first, and then as Date. Choose "Add Step" rather than "replace"
pls try
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.
