Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
perishkabb
New Member

Convert shortened month to MM and add Fiscal Year

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!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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

edhans_0-1612312159893.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

Excellent @perishkabb . Glad I was able to help.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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

edhans_0-1612312159893.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

It worked! Thank you so much!! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.