Pass number of days in the bigquery from parameter

[zzzsharepoint]

I am using Google Big Query as my connector to Power Bi and have the below query to get my data. In the query if you see I have hard coded the value as 7 to get data for last 7 days. I would like to make this more dynamic and get my result for 3 days someday it can be 10 day etc. How can I do this? Can someone show me with steps to be done here

SELECT

  project.id AS project_id,  

  (

           SELECT value

           FROM   UNNEST(labels)

           WHERE  KEY='team') AS team,

  (

           SELECT value

           FROM   UNNEST(labels)

           WHERE  KEY='service') AS service,  

    SUM(cost) AS cost

FROM `XXXX_jjjSSS_1234`

WHERE DATE(export_time) > DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

GROUP BY

  project_id, team, service

ORDER BY cost DESC

[Anonymous]

Hey @zzzsharepoint ,
In the query text, you can pass the powerQuery parameter. something like below: 
For ex., If parameter name is _paramIntervalDays then query looks something like : 

"SELECT

  project.id AS project_id,  

  (

           SELECT value

           FROM   UNNEST(labels)

           WHERE  KEY='team') AS team,

  (

           SELECT value

           FROM   UNNEST(labels)

           WHERE  KEY='service') AS service,  

    SUM(cost) AS cost

FROM `XXXX_jjjSSS_1234`

WHERE DATE(export_time) > DATE_SUB(CURRENT_DATE(), INTERVAL" & _paramIntervalDays & " DAY)

GROUP BY

  project_id, team, service

ORDER BY cost DESC"

[zzzsharepoint]

Get error as DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Could not cast literal "& LastDays &" to type INT64 at [23:61]
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
OdbcErrors=[Table

Here is the query I tried:

SELECT

  project.id AS project_id,  

  (

           SELECT value

           FROM   UNNEST(labels)

           WHERE  KEY='team') AS team,

  (

           SELECT value

           FROM   UNNEST(labels)

           WHERE  KEY='service') AS service,  

    SUM(cost) AS cost

FROM `XXXX_jjjSSS_1234`

WHERE DATE(export_time) > DATE_SUB(CURRENT_DATE(), INTERVAL" & LastDays & " DAY)

GROUP BY

  project_id, team, service

ORDER BY cost DESC

Any Idea what is wrong "LastDays" is my paremeter of text type

[Anonymous]

looks like It is a syntax error, you can try writing your as a text and pass the 'querytext' as a query parameter in your connection

The query formed looks something like: 

[zzzsharepoint]

Its not working.. have you tried it with Big Query? I created a Parameter of type TEXT and gave a default value of 7. and now I am passing the paremeter as suggested by you in my big query but always end up in error as mentioned above.