Working out week_date from a date column

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.

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