Forum Discussion
ovetteabejuela
9 years agoImpactful Individual
PowerQuery: Filter by Previous WeekEnding (a Saturday)
I would like to filter the source from January first until the previous week ending (a saturday) - how do you accomplish this?
Example - Short version:
let Source = List.Dates(#date(2016,12,1),200,#duration(1,0,0,0)), Tabled = Table.FromList(Source, Splitter.SplitByNothing(), {"Base Date"}, null, ExtraValues.Error), Typed = Table.TransformColumnTypes(Tabled,{{"Base Date", type date}}), Filtered = Table.SelectRows(Typed, each [Base Date] >= Date.StartOfYear(DateTime.Date(DateTime.LocalNow())) and [Base Date] <= Date.EndOfWeek(Date.AddWeeks(DateTime.Date(DateTime.LocalNow()),-1), Day.Sunday)) in FilteredExample - Step by step:
let Source = List.Dates(#date(2016,12,1),200,#duration(1,0,0,0)), Tabled = Table.FromList(Source, Splitter.SplitByNothing(), {"Base Date"}, null, ExtraValues.Error), Typed = Table.TransformColumnTypes(Tabled,{{"Base Date", type date}}), AddedToday = Table.AddColumn(Typed, "Today", each DateTime.Date(DateTime.LocalNow())), InsertedStartofYear = Table.AddColumn(AddedToday, "StartOfYear", each Date.StartOfYear([Today])), AddedWeekAgo = Table.AddColumn(InsertedStartofYear, "Week Ago", each Date.AddWeeks([Today],-1)), InsertedEndOfLastWeek = Table.AddColumn(AddedWeekAgo, "EndOfLastWeek", each Date.EndOfWeek([Week Ago], Day.Sunday), type date), Filtered = Table.SelectRows(InsertedEndOfLastWeek, each [Base Date] >= [StartOfYear] and [Base Date] <= [EndOfLastWeek]), RemovedColumns = Table.RemoveColumns(Filtered,{"Today", "StartOfYear", "Week Ago", "EndOfLastWeek"}) in RemovedColumns
2 Replies
- MarcelBeugCommunity Champion
Example - Short version:
let Source = List.Dates(#date(2016,12,1),200,#duration(1,0,0,0)), Tabled = Table.FromList(Source, Splitter.SplitByNothing(), {"Base Date"}, null, ExtraValues.Error), Typed = Table.TransformColumnTypes(Tabled,{{"Base Date", type date}}), Filtered = Table.SelectRows(Typed, each [Base Date] >= Date.StartOfYear(DateTime.Date(DateTime.LocalNow())) and [Base Date] <= Date.EndOfWeek(Date.AddWeeks(DateTime.Date(DateTime.LocalNow()),-1), Day.Sunday)) in FilteredExample - Step by step:
let Source = List.Dates(#date(2016,12,1),200,#duration(1,0,0,0)), Tabled = Table.FromList(Source, Splitter.SplitByNothing(), {"Base Date"}, null, ExtraValues.Error), Typed = Table.TransformColumnTypes(Tabled,{{"Base Date", type date}}), AddedToday = Table.AddColumn(Typed, "Today", each DateTime.Date(DateTime.LocalNow())), InsertedStartofYear = Table.AddColumn(AddedToday, "StartOfYear", each Date.StartOfYear([Today])), AddedWeekAgo = Table.AddColumn(InsertedStartofYear, "Week Ago", each Date.AddWeeks([Today],-1)), InsertedEndOfLastWeek = Table.AddColumn(AddedWeekAgo, "EndOfLastWeek", each Date.EndOfWeek([Week Ago], Day.Sunday), type date), Filtered = Table.SelectRows(InsertedEndOfLastWeek, each [Base Date] >= [StartOfYear] and [Base Date] <= [EndOfLastWeek]), RemovedColumns = Table.RemoveColumns(Filtered,{"Today", "StartOfYear", "Week Ago", "EndOfLastWeek"}) in RemovedColumns- ovetteabejuelaImpactful Individual
What can I say.... beautiful...
Really, I started to love DAX first and ignored M completely. These past 3 to 4 weeks has all been M! I'm already appreciating it and inspired by it - making me want to know more!!!
Thanks MarcelBeug