cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anikah
Helper II
Helper II

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

Hello All, 

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

Thanks in Advance

1 ACCEPTED SOLUTION
KNP
Super User
Super User

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"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

2 REPLIES 2
KNP
Super User
Super User

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"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

mhossain
Super User
Super User

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors