Forum Discussion

mp390988's avatar
mp390988
Post Partisan
1 year ago
Solved

Convert text to date

Hi,

 

I have the following table which is storing the TDMonth column as text.

 

 

I am trying to plot a line chart with this column being the x-axis but it's not liking it as it complaining with the following error:

 

 

What is the resolution for this please?

 

Thank You

 

Need Help
  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi mp390988 

     

    If adding the column within Powerquery:

    Date.FromText("01-" & [TDMonth])


    Within DAX Calculated column, keep in mind that it depends on the regional date format settings. If thats okay use this:

    DateColumn =
DATEVALUE(
"01 " & 'Table'[YourColumn]
)


     else:

    DateColumn = 
DATE(
    RIGHT('Table'[TDMonth],4),
    SWITCH(
        LEFT('Table'[TDMonth],3),
        "Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,
        "Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12
    ),
    1
)

7 Replies

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    1 year ago

    Convert TDMonth from text to date using this DAX:
    TDMonthDate = DATEVALUE("01-" & [TDMonth])


    Then use TDMonthDate on your x-axis. Set axis type to Continuous for proper time scaling. That’ll fix the error and make your line chart work smoothly

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Super User
    1 year ago

    Hi mp390988 

     

    If adding the column within Powerquery:

    Date.FromText("01-" & [TDMonth])


    Within DAX Calculated column, keep in mind that it depends on the regional date format settings. If thats okay use this:

    DateColumn =
DATEVALUE(
"01 " & 'Table'[YourColumn]
)


     else:

    DateColumn = 
DATE(
    RIGHT('Table'[TDMonth],4),
    SWITCH(
        LEFT('Table'[TDMonth],3),
        "Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,
        "Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12
    ),
    1
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    11 months ago

    Hi,

    In Power Query, chaage the data type of that column in the original dat table to Date.

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    11 months ago

    Hi mp390988 

    We’d like to check if you were able to go through the previous response to your issue. Please let us know if you need further clarification we’ll do our best to support you.

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      11 months ago

      Hi mp390988 

      We haven’t heard from you on the last response and was just checking back to see if your query was answered, Otherwise, will respond back with the more details and we will try to help.

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        11 months ago

        Hi mp390988 
        Just checking in to see if you had a chance to review the previous response. If you still need assistance, please let us know and we’ll be glad to help.