Day list between 2 dates without weekend

[luzsoulez]

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

[BA_Pete · Accepted solution]

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

[luzsoulez]

That was great, thanks!