Forum Discussion
Extract ISO 8601 Date-Time and Convert it to Date-Time-Format
short explanation of the initial situation: I have a table in which answers come back. In addition, I receive the information about the data type of the answer. Furthermore I get the information until when the answer must be checked. The field "Until" is given in ISO 8601 date-time format. Power BI recognizes the ISO format and formats it correctly.
| Until | Datatype | Answers |
| 2018-12-05T00:00:00.0000000+00:00 | STRING | This one is a Test |
| 2019-12-05T00:00:00.0000000+00:00 | INT | 500 |
| 2020-12-05T00:00:00.0000000+00:00 | DATETIME | 2018-12-05T00:00:00.0000000+00:00 |
Now I would like to extract the DATETIME from the answers and also format it, but unfortunately I get the following error message.
Does anyone here have a solution or have a similar problem?
Hi, DerMoe
Here it's in Power Query:#"Added Custom" = Table.AddColumn(#"Changed Type1", "TransformedAnswer", each if [Datatype] = "DATETIME" then [Answers] else null), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"TransformedAnswer", type datetime}}) in #"Changed Type2"
3 Replies
Hi, DerMoe
Here it's in Power Query:#"Added Custom" = Table.AddColumn(#"Changed Type1", "TransformedAnswer", each if [Datatype] = "DATETIME" then [Answers] else null), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"TransformedAnswer", type datetime}}) in #"Changed Type2"WOW! Thank you for that realy quick answer! You helped me a lot
DerMoe
Happy to help,
if you could please Kudo my answer to help me out, I would appreciate that.
Thank you
