Forum Discussion

jleibowitz's avatar
jleibowitz
Regular Visitor
9 years ago
Solved

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. ...
Need Help
  • jleibowitz's avatar
    jleibowitz
    9 years ago

    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's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
    jleibowitz
    Regular Visitor
    9 years ago

    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's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      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's avatar
        jleibowitz
        Regular Visitor
        9 years ago

        Everything worked! Thank you all for the help!