Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago
Solved

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
        Filtered

     

    Example - 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

  • MarcelBeug's avatar
    MarcelBeug
    Community 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
        Filtered

     

    Example - 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
    • ovetteabejuela's avatar
      ovetteabejuela
      Impactful 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