Power Query to Get Fiscal Quarter Based on Month Name

[legalhustler]

I want to create a custom column in Power Query that would have a formula to get the fiscal quarter based on the month column name in my data set.

Oct, Nov, Dec = Q1
Jan, Feb, Mar = Q2
Apr, May, Jun = Q3
Jul, Aug, Sep = Q4

As noted above, the month column in my data set only use the first three character for any given month. Can someone help me on what Power Query function I would need to use to get the Fiscal Quarter based on the three character month name?

 

[Craigc3814]

You could create a reference table then merge the two which would be the easy way

Or you could use the switch function found here https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/

Without looking at your data if it stretches across multiple years I would use the switch function then merge a year column with it.

 

[horseyride]

Assuming the column to check is called [Month] , then Add column...Custom column... and use this code

if [Month]="Jan" or [Month]="Feb" or [Month]="Mar" then "Q1" else if [Month]="Apr" or [Month]="May" or [Month]="Jun" then "Q2" else if [Month]="Jul" or [Month]="Aug" or [Month]="Sep" then "Q3" else "Q4"

that would produce something like this:

    #"Added Custom" = Table.AddColumn(#"PreviousStep", "CustomQuarter", each if [Month]="Jan" or [Month]="Feb" or [Month]="Mar" then "Q1" else if [Month]="Apr" or [Month]="May" or [Month]="Jun" then "Q2" else if [Month]="Jul" or [Month]="Aug" or [Month]="Sep" then "Q3" else "Q4")

 

[Ron Coderre]

Using a list of Month names:

MthName
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

I used this M-Code to calculate quarters:

let
    Source              = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DuplicateMthNameCol = Table.DuplicateColumn(Source, "MthName", "Qtr"),
    ConvertToDateText   = Table.TransformColumns(DuplicateMthNameCol, {{"Qtr", each "1-" & _ , type text}}),
    ConvertToDate       = Table.TransformColumnTypes(ConvertToDateText,{{"Qtr", type date}}),
    ConvertToQtr        = Table.TransformColumns(ConvertToDate,{{"Qtr", Date.QuarterOfYear}})
in
    ConvertToQtr

Is that something you can work with?