How to convert YYYYMM to character Month of the year

[sumonigupta]

I have imported the data from Google Analytics connector and I'd like to add a new coloumn which will have values like, "Jan, Feb, Mar etc" from my [Month of Year] column which is in following format: YYYYMM.

I have tried following solution, but to no avail:
1) Tried converting the [Month of Year] column into Data/Time, earlier it was Text, it won't allow me to convert into Date or Date/Time and convert the data into "Error"

2) Custom Column: 
Used following snippet

Snippet: =Date.FromText(Text.Range([Month of Year],0,4)&Text.Range([Month of Year],4,2))

Ref link: https://community.powerbi.com/t5/Desktop/How-to-convert-text-20150714-to-date/m-p/92515#M39064

None of the above solution has helped me, please recommend how do I go about solving this issue.

Regards

[v-yuezhe-msft · Accepted solution]

Hi @sumonigupta,

In your scenario, you can follow the steps to convert YYYYMM to your desired format.

1. Right click your table and select “New column”.

2. Create the new column using the formula below.

Column = SWITCH(LEFT(RIGHT('Table1'[Month of Year],4),2), "01","Jan", "02","Feb","03","Mar","04","April", "05","May","06","Jun","07","Jul","08","Aug","09","Sep","10","Oct","11","Nov","12","Dec")

Thanks,
Lydia Zhang

[Anonymous]

Assuming [Month of Year] is Text, try this:

= Table.AddColumn(Source, "Short Month", each Text.Start(Date.MonthName(Date.FromText([Month of Year]&"01")),3))

[sumonigupta]

Not an expert in Power BI, so I am assuming, I need to add a new column and execute this snippet?

I did and it gives "Table" as the value in each row, which when clicked open ups a new table with four column and 1 row!

Or do I have to execute it differently?

Please recommend

[Anonymous]

@sumonigupta,

From your first post, it looks like you've already used the Advanced Editor in Power Query to add a Custom Column, using the code snippet you reference.  Just replace that line in the query with my suggested changes and see how you go.  

If you still have problems, can you post your whole query, so I can show you exactly what to change?

Cheers,

Steve.