Forum Discussion

luzsoulez's avatar
luzsoulez
Helper I
3 years ago
Solved

Day list between 2 dates without weekend

Hi,

I have created a custom column to get all the days between 2 dates, but I want to exclude saturday and sundays

#"Added Custom" = Table.AddColumn(#"Changed Type3", "Transaction Date", each { Number.From([Start Date])..Number.From([End Date]) })

Is there a way to do that?

Here is the full code of the section, but it ends in error

#"Added Custom" = Table.AddColumn(#"Changed Type3", "Transaction Date", each { Number.From([Start Date])..Number.From([End Date]) }),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"id", "Status", "Offering", "Client", "Candidate", "Start Date", "Scheduled End", "End Date", "Transaction Date", "Revenue Cost Center", "Assignment Number", "Pay Rate $", "Client Bill Rate $", "Worker Type", "Branch", "Detailed Job Code", "Job Type", "JobOrder.owner", "Candidate.owner", "Owner", "Back Office Status", "Back Office Indicator", "End Reason", "# Changes Waiting Approval"}),
#"Expanded Transaction Date" = Table.ExpandListColumn(#"Reordered Columns", "Transaction Date"),
#"Changed Type4" = Table.TransformColumnTypes(#"Expanded Transaction Date",{{"Transaction Date", type date}}),
#"Delete Weekend" = Table.TransformColumns(#"Changed Type4",{{"Transaction Date", each Date.DayOfWeek(_,firstDayOfWeek)<5}}),

Thanks!

Luz

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi luzsoulez ,

     

    Try using this calculation in a new custom column. You should be able to remove a number of your steps once implemented:

    List.Select(
    List.Transform(
        {Number.From([Start Date])..Number.From([End Date])},
        each Date.From(_)
    ),
    each Date.DayOfWeek(_, 1) < 5
)

     

    Pete

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi luzsoulez ,

     

    Try using this calculation in a new custom column. You should be able to remove a number of your steps once implemented:

    List.Select(
    List.Transform(
        {Number.From([Start Date])..Number.From([End Date])},
        each Date.From(_)
    ),
    each Date.DayOfWeek(_, 1) < 5
)

     

    Pete