Forum Discussion

rh203's avatar
rh203
Icon for Helper I rankHelper 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) & ")),"
rh203's avatar
rh203
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity 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
    Icon for Helper I rankHelper 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

    • Jimmy801's avatar
      Jimmy801
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Hello rh203 

       

      so, when it does the job, why still not satisfied? 😉

      Parameters are useful when you transform a Query into a function. There you need a parameter. The advantage is that you use the query to change your function automatically. And when you are using a query to feed your parameter you can change the settings in your Excelsheet instead in the parameter-section

       

      Hope this helps

       

      Jimmy

      • rh203's avatar
        rh203
        Icon for Helper I rankHelper I
        5 years ago

        Hi Jimmy,

         

        I'm satisfied I have something which 'does the job' but I like to always find out the correct / best way of doing something which I might need in the future also.

         

        In an ideal world I would like to pass this value into a DAX query which is being sent to the SSAS to collect the source data - I understand this may be possible using parameters but not so sure on just using a 'list'... any ideas?

         

        thanks