Forum Discussion

Fab117's avatar
Fab117
Icon for Helper IV rankHelper IV
3 years ago
Solved

In a new column. calculate corresponding Quarter from a date

Hello,

I've a column with dates.

NB: Date in Eu format (dd.mm.yyyy)

I would like to create a new column where Power Query would calculate corresponding Quarter/year for the date on the same line.

Example:

 

TaskFinishDate	Completion by Quarter/Year	Expected result
30.11.2022		                         Q4 22
01.03.2023		                         Q1 23
15.03.2023		                         Q1 23
13.07.2023		                         Q3 23
30.09.2023		                         Q3 23
30.03.2023		                         Q1 23
16.01.2023		                         Q1 23
31.01.2023		                         Q1 23
27.12.2022		                         Q4 22
16.01.2023		                         Q1 23
15.12.2022		                         Q4 22
16.01.2023		                         Q1 23
16.01.2023		                         Q1 23
17.04.2024		                         Q2 24
08.09.2023		                         Q3 23
06.10.2023		                         Q4 23
06.10.2023		                         Q4 23
26.01.2023		                         Q1 23
09.06.2023		                         Q2 23

 

 

Do someone could help me with the formulae's?

 

Thanks a lot

 

Fab

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Fab117 ,

     

    Ok, so the basic functions you need are: Date.Year and Date.QuarterOfYear. All we need to do is apply a bit of text gymnastics to get the exact output you need:

    Text.Combine(
    {
        "Q",
        Text.From(Date.QuarterOfYear([TaskFinishDate])),
        " ",
        Text.End(Text.From(Date.Year([TaskFinishDate])), 2)
    }
)

     

    Pete

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    Hi Fab117 ,

     

    Ok, so the basic functions you need are: Date.Year and Date.QuarterOfYear. All we need to do is apply a bit of text gymnastics to get the exact output you need:

    Text.Combine(
    {
        "Q",
        Text.From(Date.QuarterOfYear([TaskFinishDate])),
        " ",
        Text.End(Text.From(Date.Year([TaskFinishDate])), 2)
    }
)

     

    Pete

    • Fab117's avatar
      Fab117
      Icon for Helper IV rankHelper IV
      3 years ago

      Hi Pete,

      Thank you for taking time looking at my question and also for the very clear detailled formaulae which will help me also with future challenges.

      Have a nice end of the day

       

      Fab