Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MBHViz
Frequent Visitor

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

Year as imported.png

Year after type change.png

Year after transformed.png

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

View solution in original post

10 REPLIES 10
MBHViz1
Regular Visitor

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
Super User
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, #

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 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)

Blank query - year to date.pngDate query.png 

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"

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

 

Date column left view.png

Date column extra comma.png

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

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"

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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors