How to extract Month and Year from column in PowerBI powerquery

Question

I have a column (monthyear) in the image below. I want to extract the Month and year from the column to put it in the new column. Note: In my dataset this information goes for every day of the year

So the new column would look like: 01/2020 01/2020 01/2020 etc.

Answer 1

In Power Query, use some of the date functions.

To get the year it will be

Date.Year([monthyear])

For the month, it will depend on how you want to format it. Using the month of June as an example:

To get 'Jun'

Date.ToText([monthyear],"MMM")

To get the month number in the format 06

Number.ToText(Date.Month([monthyear]), "00")

Just to get the number 6 it will be:

Date.Month([monthyear])

In DAX use the date functions

For year the calculated column will be:

YEAR([monthyear])

For the month:

MONTH([monthyear])

I would always do a much data transformation in Power Query when you can before it gets to the data model.