cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tomas12344
Helper I
Helper I

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

 

 

1 ACCEPTED SOLUTION

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 ....

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Community Champion
Community Champion

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 ....

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors