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 u...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    this

     

    lol

     

    FILTER('PFR Date','PFR Date'[PFR Week]=VALUE(" & Text.From(SELECTED_PFR_WEEK) & ")),"
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

      • rh203's avatar
        rh203
        Helper I
        5 years ago

        Hi Jimmy,

         

        Thanks for this - I had already managed to achieve this last night but this doesn't use parameters at all (only a named list)

         

        it's fine - it does the job, but I'm still lost as to what the purpose of a parameter with a query as source is in excel... it never picks up the values and relies on the 'current value' field anyway...

         

        Thanks for your help on this

         

        Ryan