None option in a parameter

[Anonymous]

Hello,

To simplify my problem, let's pretend that I am using accounts data. Each account has a type : A, B or C. I am using a parameter which filters my data according to these three types. To do so, I am filtering the tables with this parameter in the Query section.

What I would like to do is to add a "none" option to the parameter to get all the data without any filter and therefore be able to display the global data.

Best

[Mariusz · Accepted solution]

Hi @Anonymous 

Sure, you can use the exaple below.

Parameter query.

"none" meta [IsParameterQuery=true, List={"none", "A", "B", "C"}, DefaultValue="none", Type="Text", IsParameterQueryRequired=true]

Table query 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [types = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"types", type text}}),
    #"Filtered Rows" = if Parameter = "none" then #"Changed Type" else Table.SelectRows(#"Changed Type", each [types] =  Parameter )
in
    #"Filtered Rows"

Hope this helps!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[gtamir1]

Hi @Mariusz I am also interested in the solution. What do I have to modify in my query to have a none option?

I dint have a list like in your example.

Thanks Giora