Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 chan...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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:

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
9 years ago

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

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

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

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        9 years ago

        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's avatar
    Anonymous
    Not applicable
    9 years ago

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