none
Date.StartOfWeek not returning a DateTime value RRS feed

  • Question

  • I have a DateTime field I want to get the Start of week (starting on a Monday) from, so I use Date.StartOfWeek([TimeByDay],Day.Monday)) The result is not a DateTime field. When I remove the optional Day.Monday, the result is still not a Date.Time field.

    When I use the newly built-in graphical/menu-based "Transform | Date | Week | Start of Week", that does return a DateTime value - however, I see no way to specify I want the beginning of the week to be a Monday. Help Please?


    Ian

    Tuesday, November 22, 2016 5:16 PM

Answers

  • The menu-option returns:

    = Table.TransformColumns(#"Changed Type",{{"TimeByDay", Date.StartOfWeek, type datetime}})

    You can adjust this to:

    = Table.TransformColumns(#"Changed Type",{{"TimeByDay", each Date.StartOfWeek(_,Day.Monday), type datetime}})


    • Edited by MarcelBeug Tuesday, November 22, 2016 6:14 PM
    • Marked as answer by Ian Bruckner Tuesday, November 22, 2016 6:18 PM
    Tuesday, November 22, 2016 6:14 PM

All replies

  • The menu-option returns:

    = Table.TransformColumns(#"Changed Type",{{"TimeByDay", Date.StartOfWeek, type datetime}})

    You can adjust this to:

    = Table.TransformColumns(#"Changed Type",{{"TimeByDay", each Date.StartOfWeek(_,Day.Monday), type datetime}})


    • Edited by MarcelBeug Tuesday, November 22, 2016 6:14 PM
    • Marked as answer by Ian Bruckner Tuesday, November 22, 2016 6:18 PM
    Tuesday, November 22, 2016 6:14 PM
  • Thank you! For the life of me, I couldn't figure out how to represent the "current object", since [NameOfField] doesn't work, I hadn't caught on it's represented by an underscore.

    Ian


    Tuesday, November 22, 2016 6:19 PM
  • In the Power Query editor, click the step name that transform the date to the date of the first day of the week. In the Formula Bar, you should see an expression that looks something like:

    Table.TransformColumns(#"Changed Type",{{"TimeByDay", Date.StartOfWeek, type datetime}})

    Modify the expression to the following:

    Table.TransformColumns(#"Changed Type",{{"TimeByDay", each Date.StartOfWeek(_,Day.Monday), type datetime}})

    Tuesday, November 22, 2016 6:21 PM
  • Oops, Marcel obviously replied while I was in the process of responding. :) 
    Tuesday, November 22, 2016 7:10 PM
  • Oops, Marcel obviously replied while I was in the process of responding. :) 

    No worries, we're definitely in agreement on the solution! :-)

    Tuesday, November 22, 2016 7:31 PM
  • Nice!  Very helpful.  Just getting started with "M" but was able to open the Advanced Editor and make this change. (My client's week starts on Monday.)

    Dr. Edward M. Hanna, PMP President Data Express, Inc. Irvine, CA emhanna@sbcglobal.net

    Friday, September 29, 2017 5:01 PM