Convert Datetimeoffset to Datetime in Directquery

[Anonymous]

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?

[hnguy71 · Accepted solution]

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]

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.

[v-xuding-msft · Accepted solution]

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 · Accepted solution]

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]

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]

@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)

[Anonymous]

Can you please give me instructions step by step? I started to use PowerBI this week and I dont really know how to do it 🙂 Thanks!

[hnguy71]

@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]

Getting error

[hnguy71]

@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)