Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I
Resolver I

power query | parameter query to filter data a predefined list from google bigquery database


i get my data for period beginning 01 January 2015 from google bigquery as follows:

Source = Value.NativeQuery(GoogleBigQuery.Database()
"select * from
where (ORIGIN='LAX' or DEST='LAX')",

i'd like to create a parameter query to filter the ORIGIN and DEST from a pre-defined list when getting the data from google bigquery.

i'd appreciate any help to show me how to parameter query for that.

tks & krgds, -nik


Frequent Visitor

Dear all, 
It finaly worked  ! 

The Parameter needs to be declared as “&ParameterName&”

If this is in a where clause set as below xxxx where ‘“&ParameterName&”’


Source = Value.NativeQuery(
GoogleBigQuery.Database([BillingProject = BQ_Source]){[Name = BQ_Source]}[Data],
FROM dwh.Table AS sa
where 1=1
and sa.Country= '"&CountrySelection&"'"


Then you need a parameter name CountrySelection where you set your string



[EnableFolding = true]


thank you for your help

Super User
Super User

I think you can solve this in the following way.


When you have a datasource that provides the input you can load this table in Powerquery. Manipulate the data so that you only get one column with the desired filter values. 


When you don't have a datasource and you need to input the values yourself you can create a table:



  m_table= #table(
 type table
  {"Second value"},
   {"Third value"}
Either option would need to give you a one column table with the distinct values that you want to input in the where clause.
The next step is to convert the table to a list with the following function Table.ToList:
#"Converted to list" = #"Name previous step"[column name],
When you converted the table to a list you van use the Text.Combine formula to prepare the list as an input value for your where parameter
Inputvalue = Text.Combine( #"Converted to list",",") the ", is the seperator between the values.  You might need to add one more step to add a " before the first value. 
The result is a one column list which you can use as input for the where clausule. The native query would need to be adjusted like this:
"select * from
where (ORIGIN in (" & InputlistOrigin &" ) or DEST in ("& InputlistDEST &" ))"
Hope this is helpfull.

tks, @ChielFaber.


let me try & revert if all goes well later.

krgds, -nik


Does it work? 🙂 

hi @Olivierln & @ChielFaber,
sori fr the late reply.

unfortunately it's still not working.

if i create a parameter with a query, i get flwg error msg

'Expression.Error: A cyclic reference was encountered during evaluation.'

if i change the parameter to a list of values, i get flwg error msg:

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Unrecognized name: JFK at [4:56]

i tried to change power-query to get data from google bigquery currently is as flws:

Source = Value.NativeQuery(GoogleBigQuery.Database(){[Name="h2m-avia"]}[Data], "select * from where (ORIGIN in ( " & pmAirportSelect & ") or DEST in ( " & pmAirportSelect & "))", null, [EnableFolding=true]),

the m for the pmAirportSelect parameter is:

"ANC" meta [IsParameterQuery=true, ExpressionIdentifier=AirportList, Type="Text", IsParameterQueryRequired=true]

the m for AirpotList query list is:

Source = Table.Combine({ori, dest}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"STN", Order.Ascending}}),
STN1 = #"Sorted Rows"[STN]


the AirportList query list was generated by merging 2 files - ori & dest. these files were generated from the main dataset from google bigquery.

hope i can find a solution to this soonest as currently i hardcode the ORIGIN and DEST values.

tks, -nik


Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors