Forum Discussion

ageuffrard's avatar
ageuffrard
Frequent Visitor
4 years ago
Solved

display months values until value selected in parameter

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 ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"
  • ageuffrard's avatar
    ageuffrard
    Frequent Visitor
    4 years ago

    Thanks a lot, it works perfecly !