Forum Discussion

Maddyqc's avatar
Maddyqc
Frequent Visitor
6 years ago
Solved

Not able to convert text to date

Hi,

 

I am new to Power BI. I am not able to convert text to date. I tried calculated column method, but it requires the text to be in yyyy-mm-dd format, mine is in the other way. I tried in Power query too. Can some one help, please?. Thank you. 

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Maddyqc 

    you were close to aim

     

    DATE(
VALUE(RIGHT(cases[Date_report], 4)),
VALUE(LEFT(cases[Date_report], 2)),
VALUE(MID(cases[Date_report], 4, 2))
)

     

  • az38's avatar
    az38
    6 years ago

    Maddyqc 

    it depends on regional settings. just replace arguments

    DATE(
VALUE(RIGHT(cases[Date_report], 4)),
VALUE(MID(cases[Date_report], 4, 2)),
VALUE(LEFT(cases[Date_report], 2))
)

6 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Maddyqc 

    you were close to aim

     

    DATE(
VALUE(RIGHT(cases[Date_report], 4)),
VALUE(LEFT(cases[Date_report], 2)),
VALUE(MID(cases[Date_report], 4, 2))
)

     

      • Maddyqc's avatar
        Maddyqc
        Frequent Visitor
        6 years ago

        Hi Super User II,

         

        I still have a problem with it. Instead of taking the first two as date, it is considered as month (March). It is May 5th, instead of March 5th.How do we switch it?

    • vinodanandan's avatar
      vinodanandan
      New Member
      6 years ago

      Hi az38,

       

      Does the solution to conver text to date works with Live Query Mode.

       

      Thanks,

       

      Vinod