The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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]
How should I edit this formula to include this date filter?
Best regards
Bas
Solved! Go to Solution.
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" .
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 @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
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:
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:
Thanks Bas, that did the trick! Now I can see the Formula input.
Aaah it’s that easy 😊 Thank you very much @artemus , that was exactly what I was looking for!
Best regards
Bas
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" .
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
16 | |
13 |