Log In
Register
Home
New Member
June 14, 2018
Offline
Hi,
Trying to figure out how to convert serial date to date using power query. I am trying to add column and use the Date.From(column with serial dates) and I am getting error. For example one serial date is 43831 = 1/1/2020. In Power query it shows 43831 and I want it tom show 1/1/2020.
Any help is appreciated.
October 5, 2010
Offline
Hi Joe,
Please always supply a workbook with sample data. I'm guessing as to whether your serials are stored as text or numbers.
If you have serial numbers stored as text in a table called Table1 this will transform them to dates
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}})
in
#"Changed Type1"
if the serial numbers actually are numbers then use this
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
#"Changed Type"
But if the serials are stored as numbers in Excel then you just need to change the format of the cells to display a date rather than a number. No need to do a transformation in PQ.
see attached examples.
Regards
Phil
1 Guest(s)
