Results 1 to 5 of 5

Thread: Power Query to Get Fiscal Quarter
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,058
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query to Get Fiscal Quarter

    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 by legalhustler; Jun 28th, 2018 at 01:46 AM.

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Power Query to Get Fiscal Quarter

    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

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,058
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Get Fiscal Quarter

    Quote Originally Posted by VBA Geek View Post
    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 by legalhustler; Jun 28th, 2018 at 05:38 PM.

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Power Query to Get Fiscal Quarter

    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 by VBA Geek; Jun 29th, 2018 at 06:27 AM.

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,058
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Get Fiscal Quarter

    Quote Originally Posted by VBA Geek View Post
    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 by legalhustler; Jun 29th, 2018 at 09:30 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •