[BUG] Single BigQuery integer column generates an aggregated .json field with all columns

[DaniCons]

I have created this view in BigQuery:

create view project.dataset.view1 as
SELECT
o.first
,o.id as theid
,p.id as otherid
FROM dataset.view2 as o
JOIN dataset.view3 as p on p.whatever = o.id

When i add the data in that view to PowerBi, the column p.id it shows as Json string with all the three columns, while it should be only an integer.
This is an example of what i see:

{""v"":{""f"":[{""v"":""name""},{""v"":""11""},{""v"":""9""}]}}

In BigQuery and also in Google Data Studio and KlipFolio, it just looks fine with the integer value I'm expecting.
Can somebody shed some light on this? It's driving me nuts. Thanks.

[v-robertq-msft]

Hi,

According to the SELECT formula you used to create this view, it seems that the view do have three columns. I think you can follow this document to try to directly connect to the Power BI to select the tables and views:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-bigquery

Best Regards,

Community Support Team _Robert Qin

[Bamak]

Hi Dani

Very happy to see your message, because on my company we find ourselves a little alone facing the problems of the native big query connector

Microsoft and Simba (the subcontractor) do not seem in a hurry to correct them

For a year we have been facing 4/5 problems

- Desktop: Power query does not recognize columns whose name is similar to that of the SQL table

Gavity 2/5 - Power query issue     

- Desktop: issue (a crash popup)  after close and applying when you left merge table 2 on nullable mode  column

<--- root cause: sending SQL folding to BQ (SQL92 format) is not recognized by GCP

(limitation on BQ side)  : Severity 2/5

- Desktop and service: Some rows disappear on tables after models refreshes without warning pop-up or dataset refresh history log

  : Severity  5/5   -  issue Looks sounds  like  "bits"  limit 

- Desktop: Power query doesn' t unnest record type fields like BQ console does

Gravity 2/5 - Unnest via a BQ view       <---  Maybe your issue ? 

Dont t hesitate to contact me in order to create a group of demands ;>)   --> christophe.hervouet-ext@kering.com

[DaniCons]

Hi @Bamak 

Thank you for your answer.
I have indeed solved after renaming the columns, which confirms your findings.