How to Connect Power BI and BigQuery Billing?

Working with BigQuery as my data source is great but when connecting it to PBI it can be a bit discouraging sometimes, you have to prepare “views” in BigQuery and you have to make sure the data is set EXACTLY how you want it, otherwise you’ll have to do some fancy data-juggling in PBI. 

Recently I’ve came across a new problem, where my PBI dashboard stopped working, just like that out of the blue I received the following massage:

Data source error: {"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{ "code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"det ails":[{"code":"Message","detail":{"type":1,"value":"ODBC: ERROR [HY000] [Microsoft][BigQuery] (100) Error interacting with REST API: Quota exceeded: Your project exceeded quota for free query bytes scanned. For more information, see https://cloud.google.com/bigquery/troubleshooting- errors"}}],"exceptionCulprit":1}}} Table: myTable. 

This is weird, what do you mean by :”Your project exceeded quota for free query bytes scanned”, My BigQuery project has a billing account, and I pay every cent, since when anything is “free” with these things?

A short investigation led me to a PBI documentation on BigQuery/BowerBI connection. Here I discovered that PowerBI “By default, Power BI uses the first project from the list returned for the user” which means my dashboard was connected to the BigQuery public (and yes, free) data sets. Great, now how to fix it? This matter is things get weird, since all they give you is this encrypted line: 

“Specifying the following option in the underlying M in the Source step, which can be customized by using Power Query Editor in Power BI Desktop:" 

Source = GoogleBigQuery.Database([BillingProject="Include- Billing-Project-Id-Here"]) 

What is “underlying M”, and where exactly do I need to add this line?!?! After further investigation I came across this article which finally shed some light on the matter. The solution is as follow: Under “Transform Data” you need to open the “Advanced Editor” under the “View” tab. 

You need to change the (null) value as the PBI documentation explains to
[BillingProject=”Include-Billing-Project-Id-Here”]
WAIT, there’s more – you need to do this for each table you have (fun) and manually set the
billing project on all the tables that are connected to BigQuery

————————————————————————————

Author:   Or Blan, Director of Data, Ynet.