Google BigQuery formatting issues with Google Analytics sample dataset

[Anonymous]

So I'm trying to play around with the Google Analytics dataset found in Google BigQuery. Just a little background on this dataset: 

• Contains all kinds of data found for a website
• There are TONS of columns in original data source
  
  • Some columns are formatted just fine
  • There are also sections of columns that contain data for subsections. For example, there are sections titled "totals", "trafficSource", "device", "geoNetwork", "customDimensions", and "hits" that have subsections (ex: Column "customDimensions" has two subsection columns titled "customDimensions.index" and "customDimensions.value"

When I connect to BigQuery in Power BI and it loads the data, I'm noticing some formatting issues. Check out the screenshot below:

You'll notice that some columns are formatted just fine and it is understandable (i.e. "channelGrouping", "socialEngagementType", "fullVisitorId"). But it looks like the columns "customDimensions" and "hits" are not splitting up into their subsections and is combined into one messy column in a format I don't understand. 

When I go to look at the data source on BigQuery, however, it looks fine:

Even though the preview only has one row of data available, you can see that the "hits" column is correctly separated into its respective subsections.

How do I go about formatting the query to look more like this? Here are my ideas:

• I could compose a query in BigQuery using SQL and extract the data to a CSV file. That way I'd only get the data I need and I would import it into Power BI using a CSV instead of connecting to BigQuery.
• Format the data in Power BI by splitting the columns (but I'm not sure where to even start with the messy data)
• Is there a way to create a query and pull specific data using SQL in Power BI?

Any feedback is appreciated. Cheers! 

[v-yuta-msft]

Hi pstrand64,

To use SQL statement when connecting to Google Big Query, you can use ODBC connector instead. For details, you can refer to:

https://cloud.google.com/blog/big-data/2016/11/how-to-connect-bigquery-to-microsoft-excel-and-other-....

In addtion, ODBC driver only support import mode, so if you are using direct query mode you should using Google Big Query connector.

Regards,

Jimmy Tao

[Anonymous]

Jimmy, 

Thanks for the response, man. So what you're saying is that I can query the data with SQL from Google BigQuery inside Power BI by selecting the option "OBDC connector"?

Sorry if I'm not understanding this or asking the right questions, I'm a little new to SQL and data wrangling (also new to Power BI).