Convert shortened month to MM and add Fiscal Year

[perishkabb]

I have a column with shortened months (Oct, Nov, Dec etc) but need to add another column with Year where only Oct, Nov, Dec are 2020 and all other months are 2021.

Then I need to add both and show as MM/YYYY or 01/MM/YYYY so I can have type as date for timeline dashboard

I'm new to Power BI so a little detail would be appreciated!

[edhans]

Excellent @perishkabb . Glad I was able to help.

[edhans · Accepted solution]

See this @perishkabb . I keyed in Oct-Jan and got this table from it:

The first formula is:

if List.Contains({"Oct", "Nov", "Dec"}, [Month]) then 2020 else 2021

The second formula is:

Date.FromText([Month] & " " & Text.From([Year]))

From there you can format your date however you like. Power BI has custom date formats like Excel does. Here is the full M code as an example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzFPKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each if List.Contains({"Oct", "Nov", "Dec"}, [Month]) then 2020 else 2021),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Date.FromText([Month] & " " & Text.From([Year])))
in
    #"Added Custom1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Note that the best practice here is to not use the date column I've given above, but to use a date table that is has a 1:many relationship with that date field, then use your Date Table to do your report dimensions. You can get/create date tables from many places, but I have an article here on how to do it in Power Query that will give you about 30 rich date fields for a variety of uses.

[perishkabb]

It worked! Thank you so much!! 🙂