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.
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
Solved! Go to Solution.
Yep, M is just fine for something like this. Here's a quick stab; there might be a cleaner way to write it.
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"
Yep, M is just fine for something like this. Here's a quick stab; there might be a cleaner way to write it.
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"
@Anonymous
Thanks! I appreciate your time!
bump
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |