Custom columns in power query - Adding text to the weekofyear value

[sadavies2906]

I have created a custom column in a power query that returns the week number of a list of start dates using the following

=Date.WeekOfYear([Start Date])

Below is the output I get in the column header "WEEK", I want to get an output that looks like the column header "EXPECTED".

Not only do I need to add the text "Week" in front of the result but I also need the output to use the current day and work out if the start date is in the past with regards the week number and change those to the text "BACKLOG"

So if I opened the file today on the 20/02 which is week 8, all start dates prior to this week would return "BACKLOG", any start dates in this week would return "Week 8" and anything in the future would have its correct week number.

Hope I've explained this ok

Start Date	Week	Expected
01-Apr	14	Week 14
02-Mar	10	Week 10
30-Jan	5	BACKLOG
23-Feb	8	Week 8
22-Feb	8	Week 8
17-Feb	8	Week 8
14-Feb	7	BACKLOG
20-Feb	8	Week 8
10-Jan	2	BACKLOG
15-Jan	3	BACKLOG
02-Feb	5	BACKLOG

 

[sadavies2906]

Book1
	A	B	C
1	Need Date	Week	New Week
2	17/09/2020	Week 38	Week 41
3	17/09/2020	Week 38	Week 41
4	18/09/2020	Week 38	Week 41
5	25/09/2020	Week 39	Week 41
6	29/09/2020	Week 40	Week 41
7	30/09/2020	Week 40	Week 41
8	30/09/2020	Week 40	Week 41
9	01/10/2020	Week 40	Week 41
10	02/10/2020	Week 40	Week 41
11	02/10/2020	Week 40	Week 41
12	02/10/2020	Week 40	Week 41
13	02/10/2020	Week 40	Week 41
14	02/10/2020	Week 40	Week 41
15	05/10/2020	Week 41	Week 41
16	06/10/2020	Week 41	Week 41
17	07/10/2020	Week 41	Week 41
18	13/10/2020	Week 42	Week 42
19	14/10/2020	Week 42	Week 42
20	15/10/2020	Week 42	Week 42
21	19/10/2020	Week 43	Week 43
22	19/10/2020	Week 43	Week 43
23	21/10/2020	Week 43	Week 43
24	26/10/2020	Week 44	Week 44
25	27/10/2020	Week 44	Week 44
26	27/10/2020	Week 44	Week 44
27	05/11/2020	Week 45	Week 45
28	05/11/2020	Week 45	Week 45
29	06/11/2020	Week 45	Week 45
30	09/11/2020	Week 46	Week 46
31	09/11/2020	Week 46	Week 46
32	09/11/2020	Week 46	Week 46
33	16/11/2020	Week 47	Week 47
34	17/11/2020	Week 47	Week 47
35	17/11/2020	Week 47	Week 47
36	23/11/2020	Week 48	Week 48
37	23/11/2020	Week 48	Week 48
38	24/11/2020	Week 48	Week 48
39	02/12/2020	Week 49	Week 49
40	02/12/2020	Week 49	Week 49
41	02/12/2020	Week 49	Week 49
42	07/12/2020	Week 50	Week 50
43	07/12/2020	Week 50	Week 50
44	07/12/2020	Week 50	Week 50
45	14/12/2020	Week 51	Week 51
46	14/12/2020	Week 51	Week 51
47	15/12/2020	Week 51	Week 51
48	24/12/2020	Week 52	Week 52
49	24/12/2020	Week 52	Week 52
50	22/02/2021	Week 9	Week 9
51	22/02/2021	Week 9	Week 9
52	22/02/2021	Week 9	Week 9
Sheet1

 

[sandy666]

is that what you want?

Need Date	Week	New Week	Custom
17/09/2020	Week 38	Week 41	Week 41
17/09/2020	Week 38	Week 41	Week 41
18/09/2020	Week 38	Week 41	Week 41
25/09/2020	Week 39	Week 41	Week 41
29/09/2020	Week 40	Week 41	Week 41
30/09/2020	Week 40	Week 41	Week 41
30/09/2020	Week 40	Week 41	Week 41
01/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
05/10/2020	Week 41	Week 41	Week 41
06/10/2020	Week 41	Week 41	Week 41
07/10/2020	Week 41	Week 41	Week 41
13/10/2020	Week 42	Week 42	Week 42
14/10/2020	Week 42	Week 42	Week 42
15/10/2020	Week 42	Week 42	Week 42
19/10/2020	Week 43	Week 43	Week 43
19/10/2020	Week 43	Week 43	Week 43
21/10/2020	Week 43	Week 43	Week 43
26/10/2020	Week 44	Week 44	Week 44
27/10/2020	Week 44	Week 44	Week 44
27/10/2020	Week 44	Week 44	Week 44
05/11/2020	Week 45	Week 45	Week 45
05/11/2020	Week 45	Week 45	Week 45
06/11/2020	Week 45	Week 45	Week 45
09/11/2020	Week 46	Week 46	Week 46
09/11/2020	Week 46	Week 46	Week 46
09/11/2020	Week 46	Week 46	Week 46
16/11/2020	Week 47	Week 47	Week 47
17/11/2020	Week 47	Week 47	Week 47
17/11/2020	Week 47	Week 47	Week 47
23/11/2020	Week 48	Week 48	Week 48
23/11/2020	Week 48	Week 48	Week 48
24/11/2020	Week 48	Week 48	Week 48
02/12/2020	Week 49	Week 49	Week 49
02/12/2020	Week 49	Week 49	Week 49
02/12/2020	Week 49	Week 49	Week 49
07/12/2020	Week 50	Week 50	Week 50
07/12/2020	Week 50	Week 50	Week 50
07/12/2020	Week 50	Week 50	Week 50
14/12/2020	Week 51	Week 51	Week 51
14/12/2020	Week 51	Week 51	Week 51
15/12/2020	Week 51	Week 51	Week 51
24/12/2020	Week 52	Week 52	Week 52
24/12/2020	Week 52	Week 52	Week 52
22/02/2021	Week 9	Week 9	Week 41
22/02/2021	Week 9	Week 9	Week 41
22/02/2021	Week 9	Week 9	Week 41

if not I'll try to understand what you want to achieve

 

[sandy666]

ignore post above
so here is

Need Date	Week	New Week	Custom
17/09/2020	Week 38	Week 41	Week 41
17/09/2020	Week 38	Week 41	Week 41
18/09/2020	Week 38	Week 41	Week 41
25/09/2020	Week 39	Week 41	Week 41
29/09/2020	Week 40	Week 41	Week 41
30/09/2020	Week 40	Week 41	Week 41
30/09/2020	Week 40	Week 41	Week 41
01/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
02/10/2020	Week 40	Week 41	Week 41
05/10/2020	Week 41	Week 41	Week 41
06/10/2020	Week 41	Week 41	Week 41
07/10/2020	Week 41	Week 41	Week 41
13/10/2020	Week 42	Week 42	Week 42
14/10/2020	Week 42	Week 42	Week 42
15/10/2020	Week 42	Week 42	Week 42
19/10/2020	Week 43	Week 43	Week 43
19/10/2020	Week 43	Week 43	Week 43
21/10/2020	Week 43	Week 43	Week 43
26/10/2020	Week 44	Week 44	Week 44
27/10/2020	Week 44	Week 44	Week 44
27/10/2020	Week 44	Week 44	Week 44
05/11/2020	Week 45	Week 45	Week 45
05/11/2020	Week 45	Week 45	Week 45
06/11/2020	Week 45	Week 45	Week 45
09/11/2020	Week 46	Week 46	Week 46
09/11/2020	Week 46	Week 46	Week 46
09/11/2020	Week 46	Week 46	Week 46
16/11/2020	Week 47	Week 47	Week 47
17/11/2020	Week 47	Week 47	Week 47
17/11/2020	Week 47	Week 47	Week 47
23/11/2020	Week 48	Week 48	Week 48
23/11/2020	Week 48	Week 48	Week 48
24/11/2020	Week 48	Week 48	Week 48
02/12/2020	Week 49	Week 49	Week 49
02/12/2020	Week 49	Week 49	Week 49
02/12/2020	Week 49	Week 49	Week 49
07/12/2020	Week 50	Week 50	Week 50
07/12/2020	Week 50	Week 50	Week 50
07/12/2020	Week 50	Week 50	Week 50
14/12/2020	Week 51	Week 51	Week 51
14/12/2020	Week 51	Week 51	Week 51
15/12/2020	Week 51	Week 51	Week 51
24/12/2020	Week 52	Week 52	Week 52
24/12/2020	Week 52	Week 52	Week 52
22/02/2021	Week 9	Week 9	Week 9
22/02/2021	Week 9	Week 9	Week 9
22/02/2021	Week 9	Week 9	Week 9

is that what you want?

 

[sadavies2906]

Yes that's exactly what I want to see in terms of the data, I actually only need 2 columns though, the "NEED DATE" and "WEEK".

I only included the "New Week" to show what i needed the result to look like if that makes sense

 

[sandy666]

sure, add custom column "Week" with
if Date.Year([Need Date]) > Date.Year(DateTime.LocalNow()) then "Week "&Text.From(Date.WeekOfYear([Need Date])) else if Date.WeekOfYear([Need Date]) < Date.WeekOfYear(DateTime.LocalNow()) or Date.Year([Need Date]) < Date.Year(DateTime.LocalNow()) then "Week "&Text.From(Date.WeekOfYear(DateTime.LocalNow())) else "Week "&Text.From(Date.WeekOfYear([Need Date]))

 

[sadavies2906]

Thanks Sandy666 works like a dream

 

[sandy666]

You are welcome
It's nice to see if someone appreciate your work with Like
Have a nice day

 

[sandy666]

shorter version
if Date.IsInNextYear([Need Date]) then "Week "&Text.From(Date.WeekOfYear([Need Date])) else if Date.WeekOfYear([Need Date]) < Date.WeekOfYear(DateTime.LocalNow()) then "Week "&Text.From(Date.WeekOfYear(DateTime.LocalNow())) else "Week "&Text.From(Date.WeekOfYear([Need Date]))

 

[sandy666]

or even
"Week "&Text.From(if Date.IsInNextYear([Need Date]) then Date.WeekOfYear([Need Date]) else if Date.WeekOfYear([Need Date]) < Date.WeekOfYear(DateTime.LocalNow()) then Date.WeekOfYear(DateTime.LocalNow()) else Date.WeekOfYear([Need Date]))