change date format

[admin_xlsior]

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.

[v-juanli-msft · Accepted solution]

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.

[Anonymous]

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

[admin_xlsior]

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,

[Anonymous]

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

[admin_xlsior]

Hi,

I'm using Direct Query, so no Data tab for that table.

Thanks.

[Anonymous]

If you use DirectQuery, then you can add columns in SQL in the Advanced Options of Connection Establishment window.

In the SQL Statement Window, you can write SQL Query to add additional column to format of your date.

SELECT LEFT(CONVERT(varchar, GETDATE(),112),6)

Second, You can do it in Query Editor of adding two columns and merge together.

=Date.Year([DateKey])   // This would give you Year alone.

=Date.Month([Datekey])  // This would give you Month alone.

Select the above two created custom columns and Click on Merge Columns in the Transformation tab in Power BI.

Regards,

Pradeep