Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PC2790
Community Champion
Community Champion

JSON data from Google Big Query not interpreted correctly once extracted in Power BI

Hello guys,

 

So I am working on this piece of work where I am trying to fetch Google Big Query data in Power BI, however when I do that in normal tabular format, it comes out to be somewhat gibberish.

Something like below:

PC2790_0-1619531371845.png

This is useless to me as I cannot use thi sdata as required.

I stumbled upon a similar post -https://community.powerbi.com/t5/Desktop/How-to-resolve-Nested-Big-Query-Data/td-p/1327846 and can see that it is marked as an Solved, however I am not able to figure out how.

 

Can anyone please advise how this can be approached please?

P.S- As the data is sensitive, I won't be able to share the data. If there is any other wa, please donot hesitate to suggest.

 

Thankyou in advance

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

Finally I reached to a conclusion here where I am Unnesting the columns in Google Big Query, saving them as views and then consuming them in Power BI.

Prerequisite is that you have some background knowledge of writing queries in Google Big Query.

Some information on the queries can be found here:

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator

 

View solution in original post

3 REPLIES 3
PC2790
Community Champion
Community Champion

Finally I reached to a conclusion here where I am Unnesting the columns in Google Big Query, saving them as views and then consuming them in Power BI.

Prerequisite is that you have some background knowledge of writing queries in Google Big Query.

Some information on the queries can be found here:

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator

 

PC2790
Community Champion
Community Champion

So when I try to download the data in the offline mode as JSON format and the load in Power BI as JSON, it works fine.

So what I am not doing is UNNESTING the data as:

 

Select *, co.value
from <table> as tablename,
UNNEST(NestedColumnname) as co
 
I am looking for ways to get this query saved as view so that I can consume it in Power BI
 
v-cazheng-msft
Community Support
Community Support

Hi @PC2790 

Please check whether there are other tables in the Navigator Pane. Maybe some tables contains proper data.

 

v-cazheng-msft_0-1619665528691.png

 

Best Regards

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution . Really appreciate!

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.