powerbi connect to google bigquery shared dataset

[HASSANNAZEER]

powerbi connect to google bigquery shared dataset

How to connect to a Dataset which is shared with me

which does not show in powerBi

How to connect to "visitbasis1" ?

[v-yuezhe-msft]

@HASSANNAZEER,

I would recommend you create a ODBC data source for BigQuery, then use ODBC connector in Power BI Desktop and write SQL statement in the connector to check if you can successfully import data from shared dataset.


There is a similar blog for your reference.
https://cloud.google.com/blog/big-data/2016/11/how-to-connect-bigquery-to-microsoft-excel-and-other-...

Regards,
Lydia

[HASSANNAZEER]

SELECT
*
FROM
TABLE_DATE_RANGE([visitbasis1:archive_5833524659617792.visits_], DATE_ADD(CURRENT_TIMESTAMP(), -4, "MONTH"), CURRENT_TIMESTAMP())

SELECT
visitDate,
dayVisitsId,
visitId,
locationId,
location,
locationCode,
team,
userId,
nickname,
address,
proximity,
title,
division,
properties,
SUBSTR(STRING(timestamp), 9, 2) + ":" + SUBSTR(STRING(timestamp), 11, 2) + ":" + SUBSTR(STRING(timestamp), 13, 2) timestamp,
photoURL
FROM (
SELECT
visitDate,
dayVisitsId,
visitId,
customer.customerId locationId,
customer.name location,
customer.code locationCode,
mobileUser.tags team,
mobileUser.userId userId,
mobileUser.nickname nickname,
customer.address.street1 + IF(customer.address.street2 != "", ", " + customer.address.street2, "") + IF(customer.address.town != "", ", " + customer.address.town, "") + IF(customer.address.county != "", ", " + customer.address.county, "") + IF(customer.address.postCode != "", ", " + customer.address.postCode, "") + IF(customer.address.country != "", ", " + customer.address.country, "") address,
tasks.proximity proximity,
tasks.title title,
tasks.division division,
customer.properties properties,
MAX(tasks.tableRecords.photos.dateTime) timestamp,
GROUP_CONCAT(tasks.tableRecords.photos.imageReference.serviceURL) photoURL
FROM
[visitbasis1:archive_5833524659617792.visits]
WHERE
tasks.tableRecords.photos.dateTime IS NOT NULL
OR tasks.tableRecords.photos.imageReference.serviceURL IS NOT NULL
GROUP BY
visitDate,
dayVisitsId,
visitId,
locationId,
location,
locationCode,
team,
userId,
nickname,
address,
proximity,
title,
division,
properties)
ORDER BY
visitDate,
nickname,
location,
division,
title,
timestamp

[HASSANNAZEER]

Need help in connecting Data to PowerBi. 

[v-yuezhe-msft]

@HASSANNAZEER,

What is the result when you use ODBC data source in Power BI Desktop and write the above query in the ODBC connector?

Regards,
Lydia

[yan]

I assume OP is asking in PowerBI (service), not PowerBI Desktop.

If you use ODBC, you cannot use DirectQuery anymore. This means refreshing data locally (in desktop) and then publish - same with a gateway setup.

Pretty absurd since both PBI service and BigQuery are in the cloud, and cumbersome if you need the refresh daily.