Date string

[alexboe]

Hi,

I'm trying to import a csv file with dates formatted like: Jan 1, 2017 6:01:12 AM PST

How do I transform those dates when importing in PowerBI (and powerpivot)?

 

[MarcelBeug]

https://community.powerbi.com/t5/Desktop/Date-string/m-p/328336#M146582

let
    Source = "Jan 1, 2017 6:01:12 AM PST",
    #"Converted to Table" = #table(1, {{Source}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.BeforeDelimiter(_, " PST", 0), type text}}),
    #"Parsed Date" = Table.TransformColumns(#"Extracted Text Before Delimiter",{{"Column1", each Date.From(DateTimeZone.From(_)), type date}})
in
    #"Parsed Date"

 

[alexboe]

Hi,

Thanks for the quick response. It doesn't seem to work (token eof expected). Could you explain what this code tried to do? You first take all the text before PST and then try to transform into a date with time? How about the Jan in it? I don't think it understands its the first month as its formatted as text. Also I don't need the time stamp. Just the date.

 

[MarcelBeug]

This is how the code was created:

https://youtu.be/DvhWUC5Wk8M

 

[alexboe]

Thanks Marcel