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 to populate another column with the date of the next Friday. In other words, if today is 10/6/19, I want the new column to populate with 10/11/19.

 

Table 2: Has the day number of "1" in a column. From this number, I need to populate another column with next month's date using that day number. In other words, if today is 10/6/19, I want the new column to populate with 11/1/19.

 

Any help would be much appreciated. I know how to do this in Excel, but after reviewing a bunch of posts and a list of M date functions, I'm at a loss.

 

Thanks!

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.

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    Anonymous 

     

    Try this custom column\

     

    =Date.AddDays(
Date.EndOfWeek([ColumnName]),-1)
  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Anonymous 

     

    Table 1: Has today's date in a column. From this date I need to populate another column with the date of the next Friday. In other words, if today is 10/6/19, I want the new column to populate with 10/11/19.

     

    Solution- try this

    new_date = IF(Table[day_nm]="Monday",[today]+4,IF(Table[day_nm]="Tuseday",[today]+3,IF(Table[day_nm]="Wensday",[today]+2,IF(Table[day_nm]="Thursday",[today]+1,IF(Table[day_nm]="Friday",[today]+0)))))