Forum Discussion
Convert Datetimeoffset to Datetime in Directquery
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.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.
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.
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..
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)
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!