Forum Discussion

hood2media's avatar
hood2media
Resolver II
3 years ago

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

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-sto...
Need Help
Show and Tell
ChielFaber's avatar
ChielFaber
Super User
3 years ago

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:

 

 

let
  m_table= #table(
 type table
    [
        #"input"=text
       
    ],
 {
 {"LAX"},
  {"Second value"},
   {"Third value"}
     }
     )
 
in
  m_table
 
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 abc-stock.uk.uk
where (ORIGIN in (" & InputlistOrigin &" ) or DEST in ("& InputlistDEST &" ))"
 
Hope this is helpfull.
  • hood2media's avatar
    hood2media
    Resolver II
    3 years ago

    tks, ChielFaber.

     

    let me try & revert if all goes well later.

    krgds, -nik

     

    • Olivierln's avatar
      Olivierln
      Frequent Visitor
      3 years ago

      Does it work? 🙂 

      • hood2media's avatar
        hood2media
        Resolver II
        3 years ago

        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