Converting date formats

[Anonymous]

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!  

[vanessafvg]

@Anonymous i am assuming it didn't allow you to convert to a datetime by changing the datatype?

[Anonymous]

@vanessafvg Yes. I need to find a way to convert it using Power BI.

[MarcelBeug]

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
    DateTimeFromText

[Anonymous]

Hi @MarcelBeug. Do I make a calculated column for this? 

[MarcelBeug]

No this is actually done in the query editor.

You have a query from which your table originates.

Go to the advanced editor,

Add a comma behind the last line of your code above the "in" keyword,

Add my "DateTimeFromText" line to your code (above "in"),

In this line: adjust "Source" to the name of your last step (the one you just put a comma at the end),

Adjust the step after"in" to DateTimeFromText.

This is how to access the advanced query editor:

[Anonymous]

@MarcelBeugThank you so much for your response. My apologies for having so many questions since I'm quite having a hard time placing the correct script that your provided me and this is my first time opening the Advanced Query Editor. And the text is quite different from what I saw in the video. 

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}})
in
#"Changed Type"

[MarcelBeug · Accepted solution]

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
    DateTimeFromText

Steps taken:

[Anonymous]

Hello @MarcelBeug I just want to express my sincerest gratitude with all your help! This worked perfectly fine. Thank you so much! 

[v-ljerr-msft]

Hi @Anonymous,

Great to hear the problem got resolved!

Could you accept the corresponding reply as solution to close this thread? 

Regards