Forum Discussion

MBHViz's avatar
MBHViz
Frequent Visitor
2 years ago
Solved

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 t...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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"

lbendlin's avatar
lbendlin
Super User
2 years ago

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

MBHViz1's avatar
MBHViz1
Regular Visitor
2 years ago

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's avatar
    lbendlin
    Super User
    2 years ago

    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"