Power BI: Add bigquery service account key file

Question

I am trying to add a service account key file to access a bigquery data set in power bi. I am selecting service account login during the set up process. I added the service account email as well as pasted in the contents of the service account key file however I am getting authentication failed errors. This service account key file is confirmed to be working in other places it just does not work in power bi.

There is no documentation regarding what user roles/ permissions are needed.

The service account has the below permissions/ roles

BigQuery Data Viewer BigQuery Metadata Viewer BigQuery Job User BigQuery Read Session User

Any other idea how to get this to work?

Digestion

Answer 1

This is ridiculous that this is not documented in any sort of power bi page. It turns out that the power bi dialog box where you paste the service account key cannot handle any white space so you have to squash the json into 1 single line. Everything worked as expected afterwards. I hope this post saves someone some pain and suffering in the future.

https://w3percentagecalculator.com/json-to-one-line-converter/

Digestion

Can you add a link to the documentation on using a Google service account with Power BI? I am interested in this integration. – Bulahbulawayo

@JohnHanley Near the bottom of this page learn.microsoft.com/en-us/power-query/connectors/googlebigquery – Digestion

Bloody life saver. Thanks for this. – Engine

Answer 2

The "no newline" requirement is now documented by Power BI for the Google BigQuery connector.

As a best practice, don't paste credentials into third-party websites to strip newlines.

If you're on a Mac or Linux, just do tr -d '\n' < /path/to/serviceaccount/credentials.json. This output works correctly in Power BI.

Froemming

Answer 3

As suggested by the other answers, after downloading the service key, make sure to remove all the newlines (\n) from the file. Do NOT remove any other characters like spaces or tabs, since they are required.

For the permissions of the service account, I had to choose the following:

• bigquery data viewer
• bigquery job user
• bigquery read session user

Tullis