Forum Discussion
Unable to format year column as date
Hello All,
My year column defaults to whole number when imported.
I would like to change the type to date.
The recommended advice in other forums is to change the type to date, then transform to year.
However, when I do that, my year of 2017 becomes 1905-07-09 when the type is changed.
It then becomes 1905 when transformed to year.
I believe this inicates the value is being read as a number not a date. 2017/365 = 5.53 years (~1905)
As the year is not being recognized as a date it is not useful in quick measures, breaking down sales by year, etc.
Thanks
Mike
let
Source = Excel.Workbook(File.Contents("C:\Users\mbhet\OneDrive\Documents\#MichaelHetheringtonConsulting\Tech\Power BI\Microsoft Power BI Data Analyst\Data\AdventureWorksData.xlsx"), null, true),
Date_Sheet = Source{[Item="Date",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Date_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year1", each #date([Year],1,1),type date)
in
#"Added Custom"
10 Replies
- lbendlin
Super User
Replace the column value with #date([Year],1,1)
I think this should have been sent to you instead of replying to my own message...
Thanks for the prompt reply. I'm not understanding where to enter that. I'm attempting to use that code to generate a new column, then the old column could be deleted?
Using the new column selection, Year1 = #date([Year],1,1) returns an error message
The following syntax error occurred during parsing: Invalid token, Line 1, Offset 2, #
- lbendlin
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable Int64.Type) meta [Serialized.Text = true]) in type table [Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Year1", each #date([Year],1,1),type date) in #"Added Custom"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". Once you examined the code, replace the Source step with your own source.
Thanks for the prompt reply. I'm not understanding where to enter that. I'm attempting to use that code to generate a new column, then the old column could be deleted?
Using the new column selection, Year1 = #date([Year],1,1) returns an error message
The following syntax error occurred during parsing: Invalid token, Line 1, Offset 2, #
