Forum Discussion
Pass parameter to query
Hi
please help, how to pass parameter to a query. I have to followign situation:
- have created a parameter in Power query of PBI = Parameter1
- have a query QUERY1, which downloads a data (from SAP BW). In this query, i need to filter the data. Therefore i have added within this query the filter condition with Paramter1
- this is working fine, i can modify the value of Parameter1 within the QUERY1, that means
Let
Parameter1="20200101",
......(code of the query)
filter_field=Parameter1 (somewhere here is the filter condition base on the value of Parameter1)
..........
in ....
- but i need to call the QUERY1 multiple times and change the value of Parameter1. Need other solution then creating function and invoke this function from parametr table
- therefor i have created other query QUERY2, which calls QUERY1, like(Table1 is parameter table, where are listed the dates to filter in QUERY1)
let
parmtable=Table1,
Parameter1=parmtable{0}[Year],
result=Query1
in result
- This Query2 reads the values form Table1, but it it not passed to the Query1 with Parameter1, it is always using the default value of Parameter1
Any idea, how to modify the Parameter1 to pass values base on Table1? Or how to pass a value of parameter, which is generated outside of query?
thank you
Yes, you will need to change Query1 to receive parameter nad then call Query1 as
result=Query1(Parameter1)
Hence your Query1 would become
(Parameter1)=>
Let......(code of the query)
filter_field=Parameter1 (somewhere here is the filter condition base on the value of Parameter1)
..........
in ....
3 Replies
Parameter1=parmtable{0}[Year] - This will always pass the first value from parmtable
{0} - 1st row
{1} - 2nd row
and son
So, you just need to change 0 to appropriate value to pass the required parameter. Hence, if you need to pass the 3rd paramter, then this would become
Parameter1=parmtable{2}[Year]
You will also need to change your Query1 to receive Parameter1 as parameter.
Exactly, this is also working, but the value read from the table parmtable is not passed to the QUERY1, the QUERY1 is still using the default value of Paramter1.
Yes, you will need to change Query1 to receive parameter nad then call Query1 as
result=Query1(Parameter1)
Hence your Query1 would become
(Parameter1)=>
Let......(code of the query)
filter_field=Parameter1 (somewhere here is the filter condition base on the value of Parameter1)
..........
in ....
