Change Value of Power Query Variable for End User

[ashley_pbi]

Hello,

I hope to allow end users to change the value of variable(s) used in Power Query, whether through slicers, field parameters, or any other methods.

Some additional context:

• I imported my file through Excel (not Direct Query).
• Example use case: my variable in Power Query will serve as a threshold, where I select data with [Column 1]'s value > [Variable of interest]. 

Is this achievable in Power Query?

Really appreciate your help! Thank you.

[v-cgao-msft]

Hi @ashley_pbi ,

In Power Query, when using import mode, end users cannot directly pass parameters to dynamically change the query. Parameters in Power Query are typically set by the developer(sematic model owner) and require a refresh to apply any changes.
Please consider voting for similar ideas:
Microsoft Fabric Ideas

Or if possible, consider moving to a data source that supports the DirectQuery mode.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[AmiraBedh]

You can use a param like for example :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Column1] > Threshold)
in
    FilteredRows

[ashley_pbi]

Hello,

Thank you for your reply! To provide more context, my threshold variable will be checking across multiple columns. I want to select data that with [Column 1]'s value  > [variable of interest] or [Column 2]'s value > [variable of interest] or [Column 3]'s value > [variable of interest].

An example would be adding a threshold column:

#"Add Threshold Column" = Table.AddColumn(#"previous line", "Threshold", each if [Column 1] > [variable of interest] or [Column 2] > [variable of interest] or [Column 3] > [variable of interest] then [Column 1] else null)

Here, I would like the variable of interst to be adjustable by the end user.

Would this be possible? Thank you!