locked
Convert internation date to mm/dd/yyyy RRS feed

  • Question

  • I'm very new to Power Query so here goes.

    In a query to a SQL server, the date field is set as an International Date format of yyyymmdd and I want to convert it to an American format of mm,dd,yyyy. Any help is greatly appreciated.

    Thanks,

    Lee

    Friday, November 21, 2014 3:37 PM

Answers

  • hi..,

    You try to use below code:

    let
        Source = Table.FromList( { [Date=20140105],[Date=20140328],[Date=20140125],[Date=20140405] }, Record.FieldValues , {"Date"}),
        ChangeText = Table.TransformColumns(Source, {"Date", each Text.Insert(Text.Insert( Number.ToText(_) ,4,"/"), 7,"/") } ),
        ChangedType = Table.TransformColumnTypes(ChangeText,{{"Date", type date}})
    in
        ChangedType

    Sunday, November 23, 2014 5:59 AM
  • Hi Lee. Is it an actual date value, or is it text? You can tell by looking at the Data Type dropdown in the PQ ribbon when the column is selected.

    Ehren

    Tuesday, December 2, 2014 7:28 PM

All replies

  • hi..,

    You try to use below code:

    let
        Source = Table.FromList( { [Date=20140105],[Date=20140328],[Date=20140125],[Date=20140405] }, Record.FieldValues , {"Date"}),
        ChangeText = Table.TransformColumns(Source, {"Date", each Text.Insert(Text.Insert( Number.ToText(_) ,4,"/"), 7,"/") } ),
        ChangedType = Table.TransformColumnTypes(ChangeText,{{"Date", type date}})
    in
        ChangedType

    Sunday, November 23, 2014 5:59 AM
  • Hi Lee. Is it an actual date value, or is it text? You can tell by looking at the Data Type dropdown in the PQ ribbon when the column is selected.

    Ehren

    Tuesday, December 2, 2014 7:28 PM