Power Query to Get Fiscal Quarter

[legalhustler]

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!

 

[VBA Geek]

try this

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

 

[legalhustler]

try this

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.

="Q"&Number.Mod(Date.QuarterOfYear([Dates]),4)+1

 

[VBA Geek]

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:

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

 

[legalhustler]

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:

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!