Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

JSON datetime conversion in power query

Hi,   I am looking for 2 clear solutions.   1. Query for JSON string datetime conversion to datetime in PBI.   2. Can a ONE query be executed to convert multiple columns that are currently in J...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

JSON doesn't have a standardized datetime string format. Are you talking about ISO-8601 ? Maybe show an example of your strings?

 

And yes, transforms can be applied to multiple columns at the same time.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi lbendlin dax 

     

    Correct its ISO-8601

     

    attached Screen shot of the data presented in Power BI

     

    1592715600000

    Equates to

    06/21/2020 15:000:00

    MM/DD/YYYY HRS:MIN:SEC

     

    iv applied a few different queries but no luck as yet.

    • dax's avatar
      dax
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous , 

      You could try lbendlin 's suggestions and change it like below

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1NDI3NDUzAAGl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 10, 0, [name]/1000)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}})
in
    #"Changed Type1"

      Best Regards,
      Zoe Zhi

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.