Power Query to Get Fiscal Quarter

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
Platform
  1. Windows
I have a Date column (in this format m/dd/yyyy) in Power Query (using Excel) and would like to know how I can create a custom fiscal quarter column based on the Date column so that if a date falls between each of these months it will give the correct fiscal quarter: Oct-Dec is Q1, Jan-Mar is Q2, Apr-Jun is Q3, and Jul-Sep is Q4. For example, 10/1/2015 is Q1, 6/28/2018 is Q3, etc.

Thanks!
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
try this


Code:
let
    Source = Table.FromList(List.Dates(#date(2018,1,1),366,#duration(1,0,0,0)), 
                             Splitter.SplitByNothing(),
                             type table [Dates=Date.Type]),


    AddCol = Table.AddColumn(Source,
                             "QuarterNumber",
                              each Number.Mod(Date.QuarterOfYear([Dates]),4)+1,
                              Int64.Type)
in
    AddCol
 
Upvote 0
try this


Code:
let
    Source = Table.FromList(List.Dates(#date(2018,1,1),366,#duration(1,0,0,0)), 
                             Splitter.SplitByNothing(),
                             type table [Dates=Date.Type]),


    AddCol = Table.AddColumn(Source,
                             "QuarterNumber",
                              each Number.Mod(Date.QuarterOfYear([Dates]),4)+1,
                              Int64.Type)
in
    AddCol

Works fine for the most part. I created a custom column but also wanted a "Q" before the 1,2,3,4. I tried the following but it returns an error.

Code:
="Q"&Number.Mod(Date.QuarterOfYear([Dates]),4)+1
 
Last edited:
Upvote 0
Hello legalhustler

the modification you applied does not work because in Power Query you cannot combine a text with a number. This means that first the result given by the Number.Mod function needs to be converted into text by doing:

Text.From(Number.Mod(Date.QuarterOfYear([Dates]),4)+1)

then you can do "Q" &
Text.From(Number.Mod(Date.QuarterOfYear([Dates]),4)+1)

one final modification will be to change the column type, so the M becomes:

Code:
let
    Source = Table.FromList(List.Dates(#date(2018,1,1),366,#duration(1,0,0,0)), 
                             Splitter.SplitByNothing(),
                             type table [Dates=Date.Type]),




    AddCol = Table.AddColumn(Source,
                             "QuarterNumber",
                              each "Q" & Text.From(Number.Mod(Date.QuarterOfYear([Dates]),4)+1),
                              text type)
in
    AddCol
 
Last edited:
Upvote 0
Hello legalhustler

the modification you applied does not work because in Power Query you cannot combine a text with a number. This means that first the result given by the Number.Mod function needs to be converted into text by doing:

Text.From(Number.Mod(Date.QuarterOfYear([Dates]),4)+1)

then you can do "Q" &
Text.From(Number.Mod(Date.QuarterOfYear([Dates]),4)+1)

one final modification will be to change the column type, so the M becomes:

Code:
let
    Source = Table.FromList(List.Dates(#date(2018,1,1),366,#duration(1,0,0,0)), 
                             Splitter.SplitByNothing(),
                             type table [Dates=Date.Type]),




    AddCol = Table.AddColumn(Source,
                             "QuarterNumber",
                              each "Q" & Text.From(Number.Mod(Date.QuarterOfYear([Dates]),4)+1),
                              text type)
in
    AddCol

Good to know. Thanks you!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top