Working out week_date from a date column

[msdf]

Hi there 

Need help with adjusting the calculation for a custom column. 

We currently use: Date.AddDays([STRT_DTE],([Week]-1)*7), to work out the date of the following week , the "Projection Week" column. 

The issue now is with a new data set where there are both days and weeks mixed in the "Week" column. See "Day/Week" column below. 

It gives data at a day level for 35days (5 weeks) so WK1 is essentially week 6. 

What I would like to do is to bring back the date of the week just as the above formula does, for these dates. 

Hope someone can help amend the formula to get the desired result. 

TIA

[Anonymous · Accepted solution]

Hi,

Thanks for the concern of dufoq3 , and i want to offer some more information for user to refet to.

hello @msdf , based on your descriprion, you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdI7DoRADATRu0yMBN1m+YQrbeYDWAhx/2ssuSutpPU8c9/DmvfZi9cxjd/30nimFk0xKK4UPxQ3ijvFg+JJUQtWNAlRQpWQJXQJYUKZkCa0GW3m90Kb0Wa0GW1Gm9FmtBltgbZAW/BnRFugLbqtsm9V9qXKvlPZVypp473e8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"Day/Week" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type text}, {"Day/Week", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Start Date", type datetime}}, "en-GB"),
    #"Added Custom2" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Number.FromText(Text.Select([#"Day/Week"],{"0".."9"}))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom.1", each let 
a=Number.Mod([Custom],7),
b=if Text.Contains([#"Day/Week"],"DAY") and a=0 then Number.Round([Custom]/7) else null
in b),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Added Custom" = Table.AddColumn(#"Filled Up", "WeekNo", each if [Custom.1]=null then List.Max(#"Filled Up"[Custom.1]) +[Custom] else [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", "Projection Week", each Date.AddDays([Start Date],([WeekNo]-1)*7)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi,

Thanks for the concern of dufoq3 , and i want to offer some more information for user to refet to.

hello @msdf , based on your descriprion, you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdI7DoRADATRu0yMBN1m+YQrbeYDWAhx/2ssuSutpPU8c9/DmvfZi9cxjd/30nimFk0xKK4UPxQ3ijvFg+JJUQtWNAlRQpWQJXQJYUKZkCa0GW3m90Kb0Wa0GW1Gm9FmtBltgbZAW/BnRFugLbqtsm9V9qXKvlPZVypp473e8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"Day/Week" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type text}, {"Day/Week", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Start Date", type datetime}}, "en-GB"),
    #"Added Custom2" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Number.FromText(Text.Select([#"Day/Week"],{"0".."9"}))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom.1", each let 
a=Number.Mod([Custom],7),
b=if Text.Contains([#"Day/Week"],"DAY") and a=0 then Number.Round([Custom]/7) else null
in b),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Added Custom" = Table.AddColumn(#"Filled Up", "WeekNo", each if [Custom.1]=null then List.Max(#"Filled Up"[Custom.1]) +[Custom] else [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", "Projection Week", each Date.AddDays([Start Date],([WeekNo]-1)*7)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dufoq3]

Hi, could you provide expected output based on 2nd screenshot?

[msdf]

Hi @dufoq3 

Essentially I would like to work out Column D. Then I can work out Column E. 

Note: Days in Column B have been converted to Week number in Column D. 

Start Date	Day/Week	Projection	Wk_Number	Week
21/07/2024 12:00:00 AM	DAY1	162	1	21/07/2024
21/07/2024 12:00:00 AM	DAY2	156	1	21/07/2024
21/07/2024 12:00:00 AM	DAY3	151	1	21/07/2024
21/07/2024 12:00:00 AM	DAY4	146	1	21/07/2024
21/07/2024 12:00:00 AM	DAY5	141	1	21/07/2024
21/07/2024 12:00:00 AM	DAY6	137	1	21/07/2024
21/07/2024 12:00:00 AM	DAY7	133	1	21/07/2024
21/07/2024 12:00:00 AM	DAY8	129	2	28/07/2024
21/07/2024 12:00:00 AM	DAY9	126	2	28/07/2024
21/07/2024 12:00:00 AM	DAY10	123	2	28/07/2024
21/07/2024 12:00:00 AM	DAY11	120	2	28/07/2024
21/07/2024 12:00:00 AM	DAY12	116	2	28/07/2024
21/07/2024 12:00:00 AM	DAY13	113	2	28/07/2024
21/07/2024 12:00:00 AM	DAY14	110	2	4/08/2024
21/07/2024 12:00:00 AM	DAY15	106	3	4/08/2024
21/07/2024 12:00:00 AM	DAY16	103	3	4/08/2024
21/07/2024 12:00:00 AM	DAY17	100	3	4/08/2024
21/07/2024 12:00:00 AM	DAY18	97	3	4/08/2024
21/07/2024 12:00:00 AM	DAY19	94	3	4/08/2024
21/07/2024 12:00:00 AM	DAY20	91	3	4/08/2024
21/07/2024 12:00:00 AM	DAY21	88	3	4/08/2024
21/07/2024 12:00:00 AM	DAY22	85	4	11/08/2024
21/07/2024 12:00:00 AM	DAY23	83	4	11/08/2024
21/07/2024 12:00:00 AM	DAY24	80	4	11/08/2024
21/07/2024 12:00:00 AM	DAY25	77	4	11/08/2024
21/07/2024 12:00:00 AM	DAY26	74	4	11/08/2024
21/07/2024 12:00:00 AM	DAY27	71	4	11/08/2024
21/07/2024 12:00:00 AM	DAY28	68	4	11/08/2024
21/07/2024 12:00:00 AM	DAY29	66	5	18/08/2024
21/07/2024 12:00:00 AM	DAY30	63	5	18/08/2024
21/07/2024 12:00:00 AM	DAY31	60	5	18/08/2024
21/07/2024 12:00:00 AM	DAY32	57	5	18/08/2024
21/07/2024 12:00:00 AM	DAY33	54	5	18/08/2024
21/07/2024 12:00:00 AM	DAY34	99	5	18/08/2024
21/07/2024 12:00:00 AM	DAY35	96	5	18/08/2024
21/07/2024 12:00:00 AM	WK1	96	6	25/08/2024
21/07/2024 12:00:00 AM	WK2	73	7	1/09/2024
21/07/2024 12:00:00 AM	WK3	111	8	8/09/2024
21/07/2024 12:00:00 AM	WK4	82	9	15/09/2024
21/07/2024 12:00:00 AM	WK5	116	10	22/09/2024
21/07/2024 12:00:00 AM	WK6	88	11	29/09/2024

[dufoq3 · Accepted solution]

Hi,

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldVNasNADAXgqwSvA5Y0P5K6C3RXsg4l5P7XqJ9C8K7MA2Nv9DEa+Y39fG6mu/huYv2i9iVyXJfbfbtu37dfPR467bifVdvr+j9CuY7JoVZIOdSBOrnSKESuhDW0OYe8UONQAFkCxTLKQpNDKqUaqSoTJqSqUCjbYaVC2Q4rFrW7vkssooqFTA5VLKRxqGIhZHuIRTpnkIrslKn3msoZlEdwBnmImrmuIww6GokwgBASoTN3EiEN3kmERVxJhGHPIBHiMCuxsYwaBjcbibCdKSRCIoaTCJ2NTiLUZ5IIiUhmeo8f/RAbqwRD8Br3Lrkk3sehjuAqqRPx/kuvknF+vm3VzM9nwfIkrz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"Day/Week" = _t, Projection = _t, Week = _t]),
    DuplicatedColumn = Table.DuplicateColumn(Source, "Day/Week", "Day/Week2"),
    SplitColumnByCharacterTransition = Table.SplitColumn(DuplicatedColumn, "Day/Week2", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"DayWeek Text", "DayWeek Number"}),
    ChangedType = Table.TransformColumnTypes(SplitColumnByCharacterTransition,{{"DayWeek Number", Int64.Type}}),
    LastDay = Table.Max(Table.SelectRows(ChangedType, each [DayWeek Text] = "DAY"), {"DayWeek Number"})[DayWeek Number],
    StepBack = ChangedType,
    Ad_WeekNumber = Table.AddColumn(StepBack, "Week Number", each if [DayWeek Text] = "DAY" then Number.RoundUp([DayWeek Number]/7, 0) else LastDay/7 + [DayWeek Number], Int64.Type),
    RemovedColumns = Table.RemoveColumns(Ad_WeekNumber,{"DayWeek Text", "DayWeek Number"})
in
    RemovedColumns