Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
hi,
i get my data for period beginning 01 January 2015 from google bigquery as follows:
Source = Value.NativeQuery(GoogleBigQuery.Database()
{[Name="abc-stock"]}[Data],
"select * from abc-stock.uk.uk
where (ORIGIN='LAX' or DEST='LAX')",
null,
[EnableFolding=true])
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
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],
"SELECT
*
FROM dwh.Table AS sa
where 1=1
and sa.Country= '"&CountrySelection&"'"
Then you need a parameter name CountrySelection where you set your string
,
null,
[EnableFolding = true]
)
thank you for your help
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:
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]
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
OdbcErrors=[Table]
i tried to change power-query to get data from google bigquery currently is as flws:
let
Source = Value.NativeQuery(GoogleBigQuery.Database(){[Name="h2m-avia"]}[Data], "select * from h2m-avia.uk.uk 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:
let
Source = Table.Combine({ori, dest}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"STN", Order.Ascending}}),
STN1 = #"Sorted Rows"[STN]
in
STN1
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