Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Apply a filter to data from Bigquery when navigating to the table

Hello, I've connected to Bigquery using PowerQuery. When I try and connect to a table it's asking me to apply a filter on the date.        DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQ...
Need Help
  • artemus's avatar
    artemus
    5 years ago

    Right click Navigation in Applied Steps and choose "Insert Step after". Then add a custom step with a filter. E.g.

    = Table.SelectRows(#"Previous Step", each [Merchant] = "testmerchant")

    Replae #"Previous Step" with the text that is generated by default when you do "Insert Step After" .

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I have similar issue.  After trying "Right click Navigation in Applied Steps and choose "Insert Step after", I could not see anything where I could add custom step with filter, but a item called "Custom1" created in "APPLIED STEPS".  If I right click on "Custom1", there is nothig I can edit!

 

Not sure what I missed.

 

Best regards,

Yong

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

Make sure you have the formula bar turned on. In PowerQuery go to VIEW and enable the checkbox for FORMULA BAR. You now see an extra bar where you can add the code.


Does that do the trick?

Regards

Bas

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks Bas, that did the trick!  Now I can see the Formula input. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The issue I have is :

    "DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Cannot query over table 'xxxxxxxxx.dtrk_daily_analytics' without a filter over column(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' that can be used for partition elimination
    Details:
    DataSourceKind=GoogleBigQuery
    DataSourcePath=GoogleBigQuery
    OdbcErrors=[Table]"

     

    None of  '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' are defined in actual Schema of BigQuery table.  But at the time of table creation, the table was configured as:

    "Table type: Partitioned
    Partitioned by: Day
    Partitioned on field: _PARTITIONTIME 
    Partition filter: Required"

    So, not sure how to filter on them ('_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME') at Power BI side if adding a custom step.  Can I use SQL syntax in Formula Bar ?

     

    On BigQuery side, to query the data it would be something like:

    SELECT * FROM `xxxxxxxx.dtrk_daily_analytics` 
    WHERE DATE(_PARTITIONTIME) = "2021-02-26" LIMIT 1000
     
    Thanks,
    Yong