Forum Discussion
Unable to format year column as date
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"
Super Userlet
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)
- 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"