#PowerQuery – Add Year, Month and Day to your date table with Date.ToRecord – #PowerBI

When you build a date table in Power Query you might use the functions under Date to add year, month and day

And this will give you three steps in your Query

But we can do this a bit faster, and you will save a few clicks with your mouse

If you add a custom column using this formula

= Table.AddColumn(#”Changed Type”, “Custom”, each Date.ToRecord([Date]))

You will get a column containing a record

Then next step is to expand the record

And Voila – Year, month and day in 2 steps instead of 3.

Update – 25/10-2021

If you want to specify the datatype for each of the Year, Month and Day you can modify the formula for addColumn to

= Table.AddColumn(#”Changed Type”, “Custom”, each Date.ToRecord([Column1])
, type [ Year = number, Month = number, Day = number]
)

This will make the columns numbers

Happy Querying

6 thoughts on “#PowerQuery – Add Year, Month and Day to your date table with Date.ToRecord – #PowerBI

  1. Hello Erik,
    thanks for the tip. However when I follow your steps I do not get back the three mentioned fields as type number (int) – if I want to do that I am back with 3 as I need a “Change Type” step in addition.
    For the add column steps this is not needed as there I can define the type within the formula to add the column.
    Any idea / suggestions? What is the better way then?
    Cheers,
    Oliver

    1. Hi Oliver – If you want your year, month and day to be integers – yes you end up with the same number of steps – but then you have to change the default summarization for each of the columns to “Do Not summarize” as well.

      /Erik

    2. Hi Oliver – Found the solution if you want it as number –

      = Table.AddColumn(#”Changed Type”, “Custom”, each
      Date.ToRecord([Column1])
      , type [ Year = number, Month = number, Day = number]
      )

      The Table.AddColumn as a Type specifikation as the last argument – here you can specify the datatypes for each of the field values

      /Erik

      1. Erik, that is amazing – thank you! I would have never come up with the idea to do that in the add column step already and the expand step did not have such an option… Thanks!

  2. Pingback: Adding Year, Month, and Day to a Date Table via Date.ToRecord – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s