Forum Discussion

Mage's avatar
Mage
Frequent Visitor
1 year ago
Solved

Loop in Power Query to get date

Hi,

Basically I have a weekly plan through a whole month, the table may like this:

The plan will be handled from 1st weekday(Start Day) to 4rd weekday(End Day) each week, I want to transform them to concrete Start Dates and End Dates in the table by creating custom columns. The expected output may like:

How can I arrive it in Power Query instead of using DAX, perhaps it needs to be loopped but it confused me a lot.

 

Regards

 

General Comment
Need Help
  • slorin's avatar
    slorin
    1 year ago

    Hi Mage 

    Start Date

    Date.AddDays(Date.StartOfWeek([Date],0),[Start Day])

    End Date

    Date.AddDays(Date.StartOfWeek([Date],0),[End Day]) 

    Stéphane 

3 Replies

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Hi Mage 

    Start Date

    Date.AddDays(Date.StartOfWeek([Date],0),[Start Day])

    End Date

    Date.AddDays(Date.StartOfWeek([Date],0),[End Day]) 

    Stéphane 

    • Mage's avatar
      Mage
      Frequent Visitor
      1 year ago

      Thank you for the help, it reminds me a lot, but the actual will be more complex, I will post a new thread.

       

      Regards

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    You do not need loop in this case, and you can easily solve it bu using the next code

     

    let
    Source = Table.FromColumns({List.Dates(#date(2024,7,1),31,#duration(1,0,0,0))},{"Date"}),
    #"Added Custom" = Table.AddColumn(Source, "Start Date", each Date.StartOfWeek([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End Date", each Date.StartOfWeek([Date])+#duration(3,0,0,0))
in
    #"Added Custom1"

     

    in another casaes, if you need loop, check this video
    https://www.youtube.com/watch?v=G8PRbWuDcmQ&t=12s