Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Convert Datetimeoffset to Datetime in Directquery

Hey, this is a newbie question :) My source data contains a datetimeoffset column and Im using directquery. After my data loads up, I can't create a new hierarchy as "YYYY" , "MM", "DD" . And I cant edit my data because its a directquery.. Datetime column contains UTC so, I cant create any report based on days or months.. How can I create a datetime column?

 

Need Help
  • hnguy71's avatar
    hnguy71
    7 years ago

    Hi Anonymous 

    Are you saying that you have the text UTC inside your transaction date? 

    If it looks similar to this, you can transform the column and extract text before delimiter and then change it's type to datetime.

  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi Anonymous ,

    By my test, the type of Datetimeoffset is shown as the type of Date/Time/Zone in Power Query. When apply and close the query, the column will show Date/Time automatically. I could create formulas to get  year, month and day.

    • Calculated columns

     

    Year = YEAR(Table_4[Time])
Month = MONTH(Table_4[Time])
Year = YEAR('Table_4 (2)'[Time])
    • Measures

     

    Year = YEAR(MAX('Table_4 (2)'[Time])) 
Month = MONTH(MAX('Table_4 (2)'[Time]))
Day = DAY(MAX('Table_4 (2)'[Time]))

    If it still don't work, can you please post a dummy file or screenshots? 

     

    Best Regards,

    Xue

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    Hi Anonymous ,

    Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • hnguy71's avatar
    hnguy71
    Super User
    7 years ago

    Hi Anonymous 

    Are you saying that you have the text UTC inside your transaction date? 

    If it looks similar to this, you can transform the column and extract text before delimiter and then change it's type to datetime.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      yes, this (pic) is "Edit Data" mode and I want to use my date column without UTC because its not possible to create charts with datetimeoffset columns.. your solution is not supported in Directquery mode..

      • hnguy71's avatar
        hnguy71
        Super User
        7 years ago

        Anonymous 
        Okay I'm totally confused since your first picture shows that your Transaction Date column is a text type but in your last post it is a datetimezone type column which should be able to give you your date hierarchy. I can't recreate your issue but if you want to remove the timezone offset what you'll need to do is extract it via DAX using a calculated column with the formula below:

        TransactionDate = PATHITEM(SUBSTITUTE([Transaction Date], " ", "|", 2), 1, TEXT)
  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    Hi Anonymous ,

    By my test, the type of Datetimeoffset is shown as the type of Date/Time/Zone in Power Query. When apply and close the query, the column will show Date/Time automatically. I could create formulas to get  year, month and day.

    • Calculated columns

     

    Year = YEAR(Table_4[Time])
Month = MONTH(Table_4[Time])
Year = YEAR('Table_4 (2)'[Time])
    • Measures

     

    Year = YEAR(MAX('Table_4 (2)'[Time])) 
Month = MONTH(MAX('Table_4 (2)'[Time]))
Day = DAY(MAX('Table_4 (2)'[Time]))

    If it still don't work, can you please post a dummy file or screenshots? 

     

    Best Regards,

    Xue

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.