cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Data Type Error

 

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.

 

Capture_1.PNG

 

1 ACCEPTED SOLUTION
Super User III
Super User III

@andrewtan1993 - 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
Super User III
Super User III

@andrewtan1993 - 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Super User III
Super User III

Hello @andrewtan1993 

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors