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"

MBHViz1's avatar
MBHViz1
Regular Visitor
2 years ago

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

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"