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.

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?

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

4 Replies

  • 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