Parameterd On NativeQuery with bigquery

[Olivierln]

Dear community,

I do have difficulties to use a parameter to feed a where clause in powersuety using the native query with BiqQuery.

I am used to do it with Oracle, but it looks like it does not work with biquery.

Any tils? 

[ppm1]

You should be able to concatenate your parameter into the text string with your native query in the Advanced Editor. Have you tried that?

Pat

[Olivierln]

I do not get your answer. 

Let s say my parameter is name : country

My native query is 

Source = Value.NativeQuery(GoogleBigQuery.Database()
{[Name="abc-stock"]}[Data],
"select * from abc-stock.uk.uk
where location = - >here i want my country<-)",
null,
[EnableFolding=true])

In Source

How do you pass you parameter as string in the clause where

[ppm1]

Assuming country is a text parameter (not a list or any), this should work.

Source = Value.NativeQuery(GoogleBigQuery.Database()
{[Name="abc-stock"]}[Data],
"select * from abc-stock.uk.uk
where location = " & company,
null,
[EnableFolding=true])

In Source

Pat

[Olenard]

Hi, 

Tkx, 

I have made my test, unfortunately it doesn't get the content of the parameter. very strange

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Unrecognized name: LOE at [53:23]
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
OdbcErrors=[Table]

[Anonymous]

Not sure if this will work but when trying to pass parameters back to SQL DB I have to use the following format:

where location = '"&country&"'

be careful whe typing it as PQ does weird things like '"&country""' automatically so double check in the formula bar rather than the advanced editor

[Olenard]

So great ! working well with double '&'

where location = '"&country&"'

https://www.youtube.com/watch?v=E-WHW-QNswE