How to exclude Weekend dates (i.e. Saturday and sunday) from a date column in M query.

[anikah]

Hello All, 

Need help in excluding weekend dates from a date column in power query editor.

Thanks in Advance

[KNP · Accepted solution]

Here's the simple version.

See below and attached. Basically, add a DayOfWeekName column based on your date and filter from there.

let
  start = Number.From(#date(2021, 1, 1)),
  end = Number.From(#date(2021, 12, 31)),
  Source = {start .. end},
  #"Converted to Table" = Table.FromList(
    Source,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "Date"}}),
  #"Inserted Day Name" = Table.AddColumn(
    #"Renamed Columns",
    "Day Name",
    each Date.DayOfWeekName([Date]),
    type text
  ),
  #"Filtered Rows" = Table.SelectRows(
    #"Inserted Day Name",
    each ([Day Name] <> "Saturday" and [Day Name] <> "Sunday")
  )
in
  #"Filtered Rows"

[mhossain]

@anikah 

Networkdays, please see below link:

https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/