0 Kudos

Google BigQuery data connector is not working out-of-the-box

Status: New
by CLS New Member on ‎12-12-2018 12:04 AM

Using the Get Data wizard to connect to a Google BigQuery table results in the following DAX query:

 

let
    Source = GoogleBigQuery.Database(null),
    #"project" = Source{[Name="project-id"]}[Data],
    dataset_Schema = #"project"{[Name="dataset-id",Kind="Schema"]}[Data],
    table_Table = dataset_Schema{[Name="table-id",Kind="Table"]}[Data]
in
    table_Table

When trying to load the data, following error is displayed:

 

DataSource.Error: ODBC: ERROR [HY000] [Microsoft][BigQuery] (20) Query execution failed: Access Denied: Project another-project-id: The user stijn.claessens@ does not have bigquery.jobs.create permission in project another-project-id.

 

another-project-id is also a project available in my Google BigQuery account but it is not the project that I selected in the wizard. It is also not the project that contains the selected dataset and table. But it is the first project in the table returned by the DAX query Source = GoogleBigQuery.Database(null).

Apparently Power BI wrongly takes this first project further down the rest of the query. This seems to be a bug.

 

I got the query working by manually updating the DAX query to:

 

let
    Source = GoogleBigQuery.Database([BillingProject="project-id"]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "project-id"),
    #"projects" = #"Filtered Rows"[Data],
    #"project" = #"projects"{0},
    dataset_Schema = #"project"{[Name="dataset-id",Kind="Schema"]}[Data],
    table_Table = dataset_Schema{[Name="table-id",Kind="Table"]}[Data]
in
    table_Table

 

Comments
by Moderator v-qiuyu-msft
on ‎12-12-2018 10:25 PM

Hi @CLS,

 

Based on this article, you can see: 

 

"By default, Power BI uses the first project from the list returned for the user. To customize the behavior of the Billing Project when using it with Power BI, take the following steps:

 

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"])"

 

In your scenario, as you use Source = GoogleBigQuery.Database(null), it's reasonable to return first project 'another-project-id' for this step

 

Best Regards,
Qiuyun Yu