Hi everyone,
I've got an Excel parameter :
I created a parameter in Power Query with the value selected.
I want to use this parameter as a filter in a table, but I want the table filtered to the month selected.
If I select 4 (for the month April), I want the table filtered with Jan, Feb, Mar, Apr.
How to do that please ?
Thanks for your help, have a good day.
Alexandre
Solved! Go to Solution.
Insert below statement. Replace #"Changed Type" with your previous step and MonthParam with your Parameter name.
= Table.SelectRows(#"Changed Type", each [Month] <= MonthParam)
Make sure MonthParam is created with Decimal number option. If not, you can use Number.From(MonthParam) in above expression.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc7BEQAhCAPAXnj7EI1ganHsv40DPH47YSDniEoTd6PcdmQk6FqYAShQQMCcL1m1hlWwwKZ7wTPZsMIOrJgC88C0l2gPEXhfNTtg9D/LEiSG3PsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Sales", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Month] <= MonthParam)
in
#"Filtered Rows"
Insert below statement. Replace #"Changed Type" with your previous step and MonthParam with your Parameter name.
= Table.SelectRows(#"Changed Type", each [Month] <= MonthParam)
Make sure MonthParam is created with Decimal number option. If not, you can use Number.From(MonthParam) in above expression.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc7BEQAhCAPAXnj7EI1ganHsv40DPH47YSDniEoTd6PcdmQk6FqYAShQQMCcL1m1hlWwwKZ7wTPZsMIOrJgC88C0l2gPEXhfNTtg9D/LEiSG3PsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Sales", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Month] <= MonthParam)
in
#"Filtered Rows"
Thanks a lot, it works perfecly !
I need to see your table. Whether month column has 1, 2....12 or Jan, Feb...Dec in text?
Hi Vijay,
It's "1, 2, ... 12" as number.