Forum Discussion
Converting date formats
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:
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"
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:
Hello MarcelBeug I just want to express my sincerest gratitude with all your help! This worked perfectly fine. Thank you so much!
Hi Anonymous,
Great to hear the problem got resolved!
Could you accept the corresponding reply as solution to close this thread? :smileyhappy:
Regards
