cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Transform date to MM-YYYY


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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Regular Visitor

Re: Transform date to MM-YYYY

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!!

Power BI 2.pngPower BI.png

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Transform date to MM-YYYY

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]))



1.PNG2.PNG

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Transform date to MM-YYYY

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])

1.PNG2.PNG

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.

3.png

Best Regards,
Angelia

Highlighted
Regular Visitor

Re: Transform date to MM-YYYY

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!!

Power BI 2.pngPower BI.png

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Transform date to MM-YYYY

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]))



1.PNG2.PNG

Best Regards,
Angelia

View solution in original post

Highlighted
Regular Visitor

Re: Transform date to MM-YYYY

Everything worked! Thank you all for the help!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors