Forum Discussion
change date format
Hi guys,
I have a Direct Query table from SQL Server which I have a column Datekey (type : Date/Time) -> 1/1/2019 12:00:00 AM
How to have a new column, same with that but with format "YYYYMM" (201901)
Here what I already tried :
-> Power Query
1. Duplicate column, and use Transform, but the format in Date & Column is not suitable for what I need. Failed.
2. Add column, using Format. It seems this function not supported. Failed.
From Modeling Tab
1. Add new column in my Date Dimension, also failed.
I thought this is easy task, but already spend hours and now I am confuse since many site, using Transform, DAX, M language, right click in my new duplicate column and using locale (but there is no such options in my Power BI) but there is none seems appropriate for me. What I need is only a simple column with my formated date as "YYYYMM"
Please help.
Thanks.
Hi admin_xlsior
In Edit queries,
1.click on your column Datekey, then select Add column->Date->Date only, thus get a column named "Date.1"
2.click on column "Date.1", then select Add column->Date->Year, thus get a column named "Year"
3.click on column "Date.1", then select Add column->Date->Month, thus get a column named "Month"
4.click on column "Month", then select Add column->Extract->length, thus get a column named "Length"
5.add a conditional column
6.select two columns and merge them,
click on "Custom" first, then keep the "ctrl" button pressed, click on another column "Month",
then select Add column->merge.
Then i get a new column "Merged"
7.finally, merge two columns "Year" and "Merged", you could remove other columns but keep useful columns.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi admin_xlsior,
Please ensure that the existing date column's Data Type should be in DateTime. If it is in Varchar or NVarchar, then it will not allow you to format of the date.
If the Column is in DateTime, then you can format the column by adding new column as FORMAT(DATEKEY,"YYYYMM").
This can be achieved by adding new column in Transform or Right Click on the Date Key column and add new calculated column in the same table.
Regards,
Pradeep
Hi Pradeep,
Yes, actually I did the same, and my original column is Date/Time datatype.
So what I did is in Edit Query, Tab Add Column, new Customer column, and add this statement for formula = FORMAT([Datekey],"YYYYMM")
After I click ok, then Error : Expression.error The name 'FORMAT' wasn't recognized. Make sure it's spelled correctly.
On some resources, there is some statement saying Format is not available for Diret Query.
Thanks,
Hi admin_xlsior,
Don't go to Query Editor to do this.
We have Data page on the left side of your Power BI(Hghlighted in Yellow). Go to Data Page - > Find your DateKey Column and Right Click and Add "New Column". If will apply the format for your date. Use the same Expression given in previous post or see it in the DAX area.
Regards,
Pradeep
Hi admin_xlsior
In Edit queries,
1.click on your column Datekey, then select Add column->Date->Date only, thus get a column named "Date.1"
2.click on column "Date.1", then select Add column->Date->Year, thus get a column named "Year"
3.click on column "Date.1", then select Add column->Date->Month, thus get a column named "Month"
4.click on column "Month", then select Add column->Extract->length, thus get a column named "Length"
5.add a conditional column
6.select two columns and merge them,
click on "Custom" first, then keep the "ctrl" button pressed, click on another column "Month",
then select Add column->merge.
Then i get a new column "Merged"
7.finally, merge two columns "Year" and "Merged", you could remove other columns but keep useful columns.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
