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
Erik Svensen - Blog about Power BI, Power Apps, Power Query 
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
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
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
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!
😀
Pingback: Adding Year, Month, and Day to a Date Table via Date.ToRecord – Curated SQL