Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to transform single column mixed data?

Hello everyone, my data source is in CSV and I have a column in these files called login time and this column should only have values in time format (h:mm:ss AM/PM), however some files were saved in format of time serial.

 

Due to this, I have these two formats in the same column, how do I treat and put them all in time format (h:mm:ss AM/PM)?

 

 

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzN1WK1YlWMjSwMjKwMjBQcPRVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try DateTime.From([Column1]) otherwise Number.From([Column1])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Custom", type datetime}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Custom", type time}})
in
    #"Changed Type2"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".