Forum Discussion
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 dates in a spreadsheet, but for some reason Power Query won't transform them.
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 date}})
in
#"Changed Type"
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"
4 Replies
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"You will have to use below formula in a custom column
= Date.From(Number.From([sampledate]))quick and dirty:
Your data table is formated as text, so convert the output as number then as date
#"Changed Type" = Table.TransformColumnTypes(Source,{{"sampledate", type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"sampledate", type date}})
in
#"Changed Type2"That's not working for me unfortunately.
