Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find Next Date/Day in Power Query

I have two tables for which I need to find a date. In other words, I am trying to solve two problems which I feel are closely related.   Table 1: Has today's date in a column. From this date I need...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi battery514,

    If the date is 2019/10/7, did you want to get another column as 2019/10/18? And did you want to use day number "1"  as next month 's day? If so, you  could try to use below M code to see whether it work or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNS31DeyUNJRMlSK1YGKGBrom2GImANFjFBEDI2BQsaoQiZAIROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"day", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.DayOfWeek([date])=6 or Date.DayOfWeek([date])= 0 then Date.EndOfWeek([date],Day.Saturday) else Date.AddDays(Date.EndOfWeek([date],Day.Saturday),7)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.Date(#datetime(Date.Year([date]),Date.Month([date])+1,[day],0,0,0)))
in
    #"Added Custom1"

    Best Regards,
    Zoe Zhi

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

dax's avatar
dax
Community Support
6 years ago

Hi battery514,

If the date is 2019/10/7, did you want to get another column as 2019/10/18? And did you want to use day number "1"  as next month 's day? If so, you  could try to use below M code to see whether it work or not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNS31DeyUNJRMlSK1YGKGBrom2GImANFjFBEDI2BQsaoQiZAIROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"day", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.DayOfWeek([date])=6 or Date.DayOfWeek([date])= 0 then Date.EndOfWeek([date],Day.Saturday) else Date.AddDays(Date.EndOfWeek([date],Day.Saturday),7)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.Date(#datetime(Date.Year([date]),Date.Month([date])+1,[day],0,0,0)))
in
    #"Added Custom1"

Best Regards,
Zoe Zhi

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