Excel Forum

How To Group Date by Quarters and Concatenate Identity Number as prefix for the current year/The next year In Powerquery

Femco Onuch

Member

Members

Forum Posts: 35

Member Since:
May 6, 2022

Offline

August 31, 2022 - 7:18 am

I have two challenges, i am trying to display the current year/next year with employeed id as a prefix.Also i wish to group date by quarters in powerquery .

I have uploaded the challenge file as an attachment.

In there is the challenge and the expected outcome.

How do i group the date, considering that Ist quarter start from september, 17th this year (1st term), Second begins January,17th next year (2nd term) and Third quarter start from June 17th next year, until it begin another fresh cycle starting from Sepetember 17th and so on and so forth.

What i need ,
September17th, till January16th, display=> 1st term
From January17th till June16th display=>2nd term
From June17th till September 16th display=>3rd term
September 17th=> 1st term..(Cycle begins).

The term changes every

eight two-week periods ~= 4 months

I wish this to be automated in powerquery.

Thank you.

See Answer

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Moderators

Forum Posts: 1048

Member Since:
January 31, 2022

Offline

August 31, 2022 - 5:23 pm

Hi Remco,

Can you clarify why a start date if 17 Jan 2023 would become the 2nd term with an enrolment id for the years 2023/2024? Shouldn't that be the 2nd term for the 2022/2023 season?

And then you mention to be working with 3 terms, each containing ~4 months. Then, I assume that the 2nd term starts the 17th of May. Not the 17th of June.

If all of this is correct, please have a look at the attached file. I added two more employees to test the solution. Note that I added a small table indicating the Term for each the months, where January, May and September may fall in two terms, depending on the day of the month.

The queries are all very basic and I did not attempt to make it fancy. All was done via the User Interface. No manipulation of the underlying M-code was needed.

Riny