cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
basroozen
Helper V
Helper V

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][BigQuery] (70) Invalid query: Cannot query over table 'xxxxxx.3000.sales' without a filter over column(s) 'date' that can be used for partition elimination
Details:
    DataSourceKind=GoogleBigQuery
    DataSourcePath=GoogleBigQuery
    OdbcErrors=[Table]

 

 

 

basroozen_0-1611567590856.png

 

How should I edit this formula to include this date filter?

 

Best regards

Bas

1 ACCEPTED SOLUTION
artemus
Microsoft
Microsoft

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" .

View solution in original post

6 REPLIES 6
Yong
Frequent Visitor

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

Hi @Yong 

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

Yong
Frequent Visitor

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

 

 

Yong
Frequent Visitor

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

 

basroozen
Helper V
Helper V

Aaah it’s that easy 😊 Thank you very much @artemus , that was exactly what I was looking for!

Best regards

Bas

artemus
Microsoft
Microsoft

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" .

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors