PQ grouping 2 weeks

[shophoney]

Hi, I have created a power query calendar.

I need to group 2 weeks at a time for payroll.

Starting on Mondays.

How can I make a grouping interval be 14 days starting on Mondays.

 

[sandy666]

maybe try Index and Integer-Divide (this is idea only)

 

[lrobbo314]

Here is an example of what Sandy is talking about.

let
    Source = Table.FromList(List.Dates(#date(2019, 1, 7), 364, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Mod = Table.AddColumn(Index, "Custom", each Number.RoundUp([Index]/14,0)),
    Group = Table.Group(Mod, {"Custom"}, {{"Count", each _, type table}}),
    Combine = Table.AddColumn(Group, "Custom.1", each Text.Combine(List.Transform(Table.Column([Count],"Column1"),Text.From),",")),
    Split = Table.SplitColumn(Combine, "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6", "Custom.1.7", "Custom.1.8", "Custom.1.9", "Custom.1.10", "Custom.1.11", "Custom.1.12", "Custom.1.13", "Custom.1.14"}),
    Remove = Table.RemoveColumns(Split,{"Count"}),
    Transpose = Table.Transpose(Remove),
    PromotedHeaders = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true]),
    DOW = Table.AddColumn(PromotedHeaders, "Day Of Week", each Date.DayOfWeekName([1])),
    Order = Table.ReorderColumns(DOW,{"Day Of Week", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26"})
in
    Order

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Day Of Week	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26
2	Monday	1/7/2019	1/21/2019	2/4/2019	2/18/2019	3/4/2019	3/18/2019	4/1/2019	4/15/2019	4/29/2019	5/13/2019	5/27/2019	6/10/2019	6/24/2019	7/8/2019	7/22/2019	8/5/2019	8/19/2019	9/2/2019	9/16/2019	9/30/2019	10/14/2019	10/28/2019	11/11/2019	11/25/2019	12/9/2019	12/23/2019
3	Tuesday	1/8/2019	1/22/2019	2/5/2019	2/19/2019	3/5/2019	3/19/2019	4/2/2019	4/16/2019	4/30/2019	5/14/2019	5/28/2019	6/11/2019	6/25/2019	7/9/2019	7/23/2019	8/6/2019	8/20/2019	9/3/2019	9/17/2019	10/1/2019	10/15/2019	10/29/2019	11/12/2019	11/26/2019	12/10/2019	12/24/2019
4	Wednesday	1/9/2019	1/23/2019	2/6/2019	2/20/2019	3/6/2019	3/20/2019	4/3/2019	4/17/2019	5/1/2019	5/15/2019	5/29/2019	6/12/2019	6/26/2019	7/10/2019	7/24/2019	8/7/2019	8/21/2019	9/4/2019	9/18/2019	10/2/2019	10/16/2019	10/30/2019	11/13/2019	11/27/2019	12/11/2019	12/25/2019
5	Thursday	1/10/2019	1/24/2019	2/7/2019	2/21/2019	3/7/2019	3/21/2019	4/4/2019	4/18/2019	5/2/2019	5/16/2019	5/30/2019	6/13/2019	6/27/2019	7/11/2019	7/25/2019	8/8/2019	8/22/2019	9/5/2019	9/19/2019	10/3/2019	10/17/2019	10/31/2019	11/14/2019	11/28/2019	12/12/2019	12/26/2019
6	Friday	1/11/2019	1/25/2019	2/8/2019	2/22/2019	3/8/2019	3/22/2019	4/5/2019	4/19/2019	5/3/2019	5/17/2019	5/31/2019	6/14/2019	6/28/2019	7/12/2019	7/26/2019	8/9/2019	8/23/2019	9/6/2019	9/20/2019	10/4/2019	10/18/2019	11/1/2019	11/15/2019	11/29/2019	12/13/2019	12/27/2019
7	Saturday	1/12/2019	1/26/2019	2/9/2019	2/23/2019	3/9/2019	3/23/2019	4/6/2019	4/20/2019	5/4/2019	5/18/2019	6/1/2019	6/15/2019	6/29/2019	7/13/2019	7/27/2019	8/10/2019	8/24/2019	9/7/2019	9/21/2019	10/5/2019	10/19/2019	11/2/2019	11/16/2019	11/30/2019	12/14/2019	12/28/2019
8	Sunday	1/13/2019	1/27/2019	2/10/2019	2/24/2019	3/10/2019	3/24/2019	4/7/2019	4/21/2019	5/5/2019	5/19/2019	6/2/2019	6/16/2019	6/30/2019	7/14/2019	7/28/2019	8/11/2019	8/25/2019	9/8/2019	9/22/2019	10/6/2019	10/20/2019	11/3/2019	11/17/2019	12/1/2019	12/15/2019	12/29/2019
9	Monday	1/14/2019	1/28/2019	2/11/2019	2/25/2019	3/11/2019	3/25/2019	4/8/2019	4/22/2019	5/6/2019	5/20/2019	6/3/2019	6/17/2019	7/1/2019	7/15/2019	7/29/2019	8/12/2019	8/26/2019	9/9/2019	9/23/2019	10/7/2019	10/21/2019	11/4/2019	11/18/2019	12/2/2019	12/16/2019	12/30/2019
10	Tuesday	1/15/2019	1/29/2019	2/12/2019	2/26/2019	3/12/2019	3/26/2019	4/9/2019	4/23/2019	5/7/2019	5/21/2019	6/4/2019	6/18/2019	7/2/2019	7/16/2019	7/30/2019	8/13/2019	8/27/2019	9/10/2019	9/24/2019	10/8/2019	10/22/2019	11/5/2019	11/19/2019	12/3/2019	12/17/2019	12/31/2019
11	Wednesday	1/16/2019	1/30/2019	2/13/2019	2/27/2019	3/13/2019	3/27/2019	4/10/2019	4/24/2019	5/8/2019	5/22/2019	6/5/2019	6/19/2019	7/3/2019	7/17/2019	7/31/2019	8/14/2019	8/28/2019	9/11/2019	9/25/2019	10/9/2019	10/23/2019	11/6/2019	11/20/2019	12/4/2019	12/18/2019	1/1/2020
12	Thursday	1/17/2019	1/31/2019	2/14/2019	2/28/2019	3/14/2019	3/28/2019	4/11/2019	4/25/2019	5/9/2019	5/23/2019	6/6/2019	6/20/2019	7/4/2019	7/18/2019	8/1/2019	8/15/2019	8/29/2019	9/12/2019	9/26/2019	10/10/2019	10/24/2019	11/7/2019	11/21/2019	12/5/2019	12/19/2019	1/2/2020
13	Friday	1/18/2019	2/1/2019	2/15/2019	3/1/2019	3/15/2019	3/29/2019	4/12/2019	4/26/2019	5/10/2019	5/24/2019	6/7/2019	6/21/2019	7/5/2019	7/19/2019	8/2/2019	8/16/2019	8/30/2019	9/13/2019	9/27/2019	10/11/2019	10/25/2019	11/8/2019	11/22/2019	12/6/2019	12/20/2019	1/3/2020
14	Saturday	1/19/2019	2/2/2019	2/16/2019	3/2/2019	3/16/2019	3/30/2019	4/13/2019	4/27/2019	5/11/2019	5/25/2019	6/8/2019	6/22/2019	7/6/2019	7/20/2019	8/3/2019	8/17/2019	8/31/2019	9/14/2019	9/28/2019	10/12/2019	10/26/2019	11/9/2019	11/23/2019	12/7/2019	12/21/2019	1/4/2020
15	Sunday	1/20/2019	2/3/2019	2/17/2019	3/3/2019	3/17/2019	3/31/2019	4/14/2019	4/28/2019	5/12/2019	5/26/2019	6/9/2019	6/23/2019	7/7/2019	7/21/2019	8/4/2019	8/18/2019	9/1/2019	9/15/2019	9/29/2019	10/13/2019	10/27/2019	11/10/2019	11/24/2019	12/8/2019	12/22/2019	1/5/2020
Sheet2

 

[sandy666]

@lrobbo314
try with DateTime.LocalNow() and defined End date but not hardcoded start date and duration
2020 = 366 days

 

[sandy666]

Thanks @lrobbo314 ,
OP didn't say what kind of Calendar he/she has and what will be the end result so hard to say what kind of solution should be