Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Converting ISO8601 condensed date/time

I am trying to import an external live data source which records timestamps in the condensed format, i.e. YYYYMMDDhhmmss. An example is 20190826080000. This is a valid format specified in ISO 8601. Power BI interprets this as a decimal number and formatting as Date or Date/Time gives an error. Is there a simple way to convert this to YYYY-MM-DDTHH:mm:ss?

Need Help

1 Reply

  • mussaenda's avatar
    mussaenda
    Community Champion
    7 years ago

    try this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDSwMDIzsDAAAqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 8}, false), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type date}, {"Column1.2", type time}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Column1.1", type text}, {"Column1.2", type text}}, "en-GB"),{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}})
in
    #"Changed Type1"

    Ah, sorry. I am not sure if this will work on a live data.