Data Type Error

[Anonymous]

Dear community users,

I am facing with a data type error where I choose the data type as text but it doesn't accept. The Eventtime data contains both time data and some data which is written as "TBA". Please help.

[edhans · Accepted solution]

@Anonymous - I've never seen data that couldn't be stored as a text type. Did you mean you couldn't convert it to date? That is a problem with "TBA" as a value. You need to first select the column and replace TBA with null (the actual word 'null' in all lower case), and then convert the column to the datetime format. if you only want the date, then convert to date again and it will truncate the time.

You have to get rid of that TBA though.

[Jimmy801]

Hello @Anonymous 

you cannot transform the columntype do date, if there is date and text in one column. Use TransformColumns to transform date to date and text to text. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQMzTQMzJQitWJVjIyQOKEODmCaWO4YCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date and text" = _t]),

    TransformColumn = Table.TransformColumns
    (
        Source,
        {
            {
                "Date and text",
                each try Date.From(_, "de-DE") otherwise Text.From(_, "de-DE"),
                type any

            }
        }
    )
in
    TransformColumn

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy