Forum Discussion

rh203's avatar
rh203
Helper I
5 years ago
Solved

Parameter with Query as Source

Hi,

 

im trying to create a basic parameter to feed into a filter for a table at refresh runtime in power query.

 

I have created a list from a table in excel and then created a parameter which uses this query as the source.

 

The list has a set of values that I would like the users to select from when the query is executed but I am completely confused as to how a parameter which uses a query as the source is expected to work.

 

It doesn't ask for any input from the user, nor does it show the options to select from in the parameters management screen.

 

am I missing something? I would have expected the whole point of a parameter with a query as the source would be to do this at runtime and not rely on a user to input the parameter manually in the 'current value' section.

 

not at my machine right now so can't post screenshots but it is as simple as the above...

 

thanks 

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    this

     

    lol

     

    FILTER('PFR Date','PFR Date'[PFR Week]=VALUE(" & Text.From(SELECTED_PFR_WEEK) & ")),"

18 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    5 years ago

    If you have a query that you want to use to supply the validated list to a Parameter, then you need to convert the query to a list. There is a button in Power Query that will do that for you. After that, you can edit the parameter and select the Query as the source.

    But even so, it does NOT give you that validated list in the Manage Parameters dialog box. That list of validated values only shows up after you do a Close and Apply, then go to Edit Paramters option under Transform Data on the Home menu.

     

    Hope that helps

    • rh203's avatar
      rh203
      Helper I
      5 years ago

      Thanks Todd,

       

      I managed to get that far - I'm actually working in excel power query for this one so I'm not sure how an end user would change the parameter from the validated list within ther parameter from the excel interface?

       

      Maybe its just a PBI thing?

       

      Thanks

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello rh203 

         

        in Excel you have the chance to change this kind of parameters and then reload with the new setting. In Power BI there is no chance to use the input in your visuals in Power Query. In Excel you can do something like this

        let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Value", type any}, {"Selection", type any}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([Selection] = "x"))[Value]{0}
in
    #"Gefilterte Zeilen"

         

        Load this table into Power Query and filter by Selection=x and pickup the value like YourTable[Value]{0} or you create a table with one cell and a dropdown and load it to power query. In this scenario you don't need any filters, just the reference as above mentioned.

         

        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy