Unable to format year column as date

[MBHViz]

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

[MBHViz1]

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]

Replace the column value with #date([Year],1,1)

[MBHViz1]

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.

[MBHViz1]

Thanks for that code.

(1) When entered as a blank query, the Year column still defaults to a whole number

(2) When adding to existing code, what is the modification when a prior "let" exists? (promote headers)

 

[lbendlin]

Change the reference.  Remove the last two rows of your code, then add a comma, and then the code below.

#"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"

[MBHViz1]

Thanks for the additional help. The extra comma and new code was added. The second let is still being identified as an error.

[lbendlin]

Please post the code in a usable form, not as a screenshot.

[MBHViz1]

Here it is

The added comma is after Year = t

This reference appears at the bottom "Token Eof expected. Show error

When selected the second let is highlighted

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])
in
#"Promoted Headers"

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(#"Promoted Headers",{{"Year", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year1", each #date([Year],1,1),type date)
in
#"Added Custom"

[lbendlin · Accepted solution]

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"