In a new column. calculate corresponding Quarter from a date

[Fab117]

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

[BA_Pete · Accepted solution]

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]

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