Forum Discussion

justlogmein's avatar
justlogmein
Icon for Helper III rankHelper III
4 years ago
Solved

How to convert these serial numbers to dates?

I have a list of dates in serial number form that I can't seem to convert into a date format in Power Query. I can easily convert them using formulas in a spreadsheet, or just by formatting them as d...
Need Help
  • jNilsen's avatar
    jNilsen
    4 years ago

    the source as number was wrong so i deleted it. sorry for the confusion. the full query:

     

    let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRBDsMwCAT/knMPSYDEeUuU/3+jh6peKkaot5VtYHcB3/fifvhYntcHbRPtX2Q+0TnRNdGot6EsXt+lalADz7YuNnaoK3QBl2NGgCLyIFWLmtmsxipfrKAoQNFRmbpVzsQvrOZTNTmkLOIs15TFoqur24AupHm5KvK11pUbOktZiJXDWUC+WS05Oaq2VHdOhPqROgjTLkWply3nNJOwFUmHVwZBE6E5pU2BbVS3vHUyaOqinhltGW38RGnq1qoj3bZzJScx4qyK0pZFxzTIK/A+OWTwTn/iWVU6/Hp/I1Jk7TtrI4jLgFv6wxwidqgGfyftzC963g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sampledate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sampledate", type number}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"sampledate", type date}})
    in
    #"Changed Type2"

jNilsen's avatar
jNilsen
Regular Visitor
4 years ago

the source as number was wrong so i deleted it. sorry for the confusion. the full query:

 

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRBDsMwCAT/knMPSYDEeUuU/3+jh6peKkaot5VtYHcB3/fifvhYntcHbRPtX2Q+0TnRNdGot6EsXt+lalADz7YuNnaoK3QBl2NGgCLyIFWLmtmsxipfrKAoQNFRmbpVzsQvrOZTNTmkLOIs15TFoqur24AupHm5KvK11pUbOktZiJXDWUC+WS05Oaq2VHdOhPqROgjTLkWply3nNJOwFUmHVwZBE6E5pU2BbVS3vHUyaOqinhltGW38RGnq1qoj3bZzJScx4qyK0pZFxzTIK/A+OWTwTn/iWVU6/Hp/I1Jk7TtrI4jLgFv6wxwidqgGfyftzC963g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sampledate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"sampledate", type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"sampledate", type date}})
in
#"Changed Type2"