Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
Year = YEAR(Table_4[Time]) Month = MONTH(Table_4[Time]) Year = YEAR('Table_4 (2)'[Time])
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 ,
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.
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.
Year = YEAR(Table_4[Time]) Month = MONTH(Table_4[Time]) Year = YEAR('Table_4 (2)'[Time])
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!
@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
Getting error
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |