Forum

How To Group Date b...
 
Notifications
Clear all

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

3 Posts
2 Users
0 Reactions
169 Views
(@prosperchild)
Posts: 35
Trusted Member
Topic starter
 
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.

 
 
Posted : 31/08/2022 5:18 pm
Riny van Eekelen
(@riny)
Posts: 1194
Member Moderator
 

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

 
Posted : 01/09/2022 3:23 am
(@prosperchild)
Posts: 35
Trusted Member
Topic starter
 

@ Riny van,Thank you 

 
Posted : 01/09/2022 1:22 pm
Share: