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.
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.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @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
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |