Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
Need help in excluding weekend dates from a date column in power query editor.
Thanks in Advance
Solved! Go to 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"
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 ;). |
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. | Proud to be a 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 ;). |
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. | Proud to be a Super User! |
@Anonymous
Networkdays, please see below link:
https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |