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 ...
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.

hnguy71's avatar
hnguy71
Super User
7 years ago

Anonymous 
1. Under MODELING ribbon tab, choose NEW COLUMN

2. Inside the formula bar for the new column, replace the default formula with this:

TransactionDate = PATHITEM(SUBSTITUTE([Transaction Date], " ", "|", 2), 1, TEXT)

3. Under MODELING ribbon tab again, change DATA TYPE to DATE/TIME

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Getting error

 

  • hnguy71's avatar
    hnguy71
    Super User
    7 years ago

    Anonymous 
    Totally forgot that while in DirectQuery mode, the use of Parent-Child functions are disallowed. Is there a reason why you need to be in DirectQuery vs Import? In any case, try this instead:

    TransactionDate = LEFT(SUBSTITUTE([Transaction Date], " ", "|", 2), SEARCH("|", SUBSTITUTE('[Transaction Date], " ", "|",2))-1)