Forum Discussion
Converting date formats
Hi,
I have currently have this date format in my data set which I imported through a .CSV file. And Power BI reads the date format as text as shown in the screenshot below:
I want to change into date/time format since I need to get the hourly data from my data set. How do i transform my current data set? Thanks in advance!
The video was only intended to show how to access the query editor.
Otherwise just follow the text I provided and this will be the resulting code that should be working fine:
let Source = Csv.Document(File.Contents("C:\Users\Jeano\Downloads\PRESTIGE_LOG_RAW.csv"),[Delimiter=",", Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ENTRY_NO", Int64.Type}, {"CARD_NO", type number}, {"MEMBER_NAME", type text}, {"EXPIRY_DATE", type text}, {"BIRTH_DATE", type text}, {"TIME_IN", type text}, {"NO_OF_GUEST", Int64.Type}, {"GRA_ID", Int64.Type}, {"REQUEST", type text}, {"VIOLATION", type text}, {"BRANCH_ID", Int64.Type}, {"BRANCH_CODE", Int64.Type}, {"BRANCH_NAME", type text}}), DateTimeFromText = Table.TransformColumns(#"Changed Type",{{"TIME_IN", each DateTime.FromText(Text.ReplaceRange(_,7,1," "),"en-US"), type datetime}}) in DateTimeFromTextSteps taken:
9 Replies
Anonymous i am assuming it didn't allow you to convert to a datetime by changing the datatype?
The issue is the first colon ( : ) : adjust that to a space and then the string can be converted to text.
I added culture code "en-US", just to be sure. Maybe you can leave it out or use another culture code.
let Source = #table(type table[TIME_IN = text],{{"01DEC16:16:57:09"}}), DateTimeFromText = Table.TransformColumns(Source,{{"TIME_IN", each DateTime.FromText(Text.ReplaceRange(_,7,1," "),"en-US"), type datetime}}) in DateTimeFromTextHi MarcelBeug. Do I make a calculated column for this?
vanessafvg Yes. I need to find a way to convert it using Power BI.
