Transform date to MM-YYYY

[jleibowitz]

Trying to transform a date in the following format '01/01/2017' to year/month. It can be in any of the following formats: 2017-01, 201701, Jan 2017. Some of the values in the date field as null.

Tried the following formula but get an error message that 'Table' is incorrect.
Year-Mon = FORMAT('Table'[Date], "YYYY-MM")

Also tried to concatenate Date.Year and Date.Month but received an error that power bi did not recognize 'concatenate'.

Questions:
1.      How to extract year/month
2.      Will null values cause an error? Does that need to be built into the formula?

[jleibowitz · Accepted solution]

Thank you so much for the quick response. My date type is definately date. Can you confirm what I should be listing as 'table'? I am connecting to an excel file and the tab I am using is called 'Sheet1'. I have no other data files, connections, etc. 

If I enter the following formula, I recieve the error below. Is my syntax correcr? =FORMAT(Table[date], "YYYY-MM")

Expression.Error: The name 'FORMAT' wasn't recognized.  Make sure it's spelled correctly.

Pictures attached for both ways I have tried this. 

Thank you!!

[v-huizhn-msft]

Hi @jleibowitz,

I try to reproduce your scenario and get expected result.

YYYY-MON = FORMAT(Table3[Date],"YYYY-MM")

Column = CONCATENATE(Table3[Date].[Year],Table3[Date].[Month])

concatenate is a DAX function, it's weird to recieve the error message. Please confirm your date column is date type

It still workd fine even there is null value, please share your screenshot for further analysis.



Best Regards,
Angelia

[jleibowitz · Accepted solution]

Thank you so much for the quick response. My date type is definately date. Can you confirm what I should be listing as 'table'? I am connecting to an excel file and the tab I am using is called 'Sheet1'. I have no other data files, connections, etc. 

If I enter the following formula, I recieve the error below. Is my syntax correcr? =FORMAT(Table[date], "YYYY-MM")

Expression.Error: The name 'FORMAT' wasn't recognized.  Make sure it's spelled correctly.

Pictures attached for both ways I have tried this. 

Thank you!!

[v-huizhn-msft · Accepted solution]

Hi @jleibowitz,

For my test, I create a calculated column using DAX formula in Power BI datamodel, while you create a custom column in Query Editor. DAX and Power Query are different computer language. Please clear them. If you want to create a custom column using Query statement, you should use the following formula.

=Number.ToText(Date.Year([Date]))&"-"&Number.ToText(Date.Month([Date]))

Best Regards,
Angelia

[jleibowitz]

Everything worked! Thank you all for the help!