# Power Query to Get Fiscal Quarter Based on Month Name



## legalhustler (Dec 4, 2018)

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 (Dec 4, 2018)

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 (Dec 4, 2018)

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 (Dec 4, 2018)

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?


----------

