Powerquery command to convert date format

Question

I have a column Date of the following format 06-08-2000.

I need to convert into the following format : dd-mmm-yyyy where mmm would be {Jan, Feb, Mar, ...}

So the output would be 06-Aug-2000.

I am totally new to powerquery and this is my only task using it. Any help would be really appreciated. Thanks in advance.

Answer 1

I guess your date column name is "dt". Your input format for the date "06-08-2000" (dd-mm-yyyy your case) is not a recognizable date format in power query. So you have to create a actual date formatted value first from your input date. Then with some couple of more steps you will get your required output

Step-1: Create a new column "new_date" in power query as below-

Step-2: Change the data type of newly created column "new_date" to "Date" type

Step-3: Go back to report by clicking "Close & Apply" button.

Step-4: New create a custom column using following DAX in the report

custom_date = FORMAT(your_table_name[new_date],"dd-mmm-yyyy")

Now you have your required formatted date.

Answer 2

In Power BI select your column. Then you have a tab "Column tools". Select it and you will see a format option. Enter dd-mmm-yyyy

Answer 3

Assuming your original date is in column date, in Powerquery, add a Add Column ... Custom Column ... with below formula to convert

= Date.ToText([date],"dd-MMM-yyyy")