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

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

MBHViz1's avatar
MBHViz1
Regular Visitor
2 years ago

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