Parameter Scenario - Not sure which route to take

[brs09j]

I am currently getting my data from a database connection. I would like the data automatically filtered to have the current year, the current year -1 and the current year +1. So, if I logged in today the filter would include 2016, 2017 and 2018.

I know I can use TODAY ( ), YEAR () and BETWEEN but was wondering what's the best route to go about this in regards to parameters? Do I need to use M in this scenario? I created a table with M using the following

let 
 

 Source = #table(  
    {"Year"},  
      {  
          {2015},
          {2016},  
          {2017},
          {2018}  
      }) 

in 

    Source

I wasn't sure if you can only use M to update a table/query created using M

[Anonymous · Accepted solution]

Yep, M is just fine for something like this. Here's a quick stab; there might be a cleaner way to write it.

Last year, this year, and next year

I started with a blank query and just wrote the first line. The next two are autogenerated by the GUI after I converted to table and renamed the column. You could leave this as a list if you're using it in other M queries.

let
    Source = List.Transform({-1..1}, each Date.Year(Date.AddYears(DateTime.LocalNow(), _))),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Year"}})
in
    #"Renamed Columns"

[brs09j]

@Anonymous

Thanks! I appreciate your time!

[brs09j]

bump