Import a nested table from Google Big Query

[Anonymous]

Hi,

I've been trying to import a nested table from Google Big Query, but all I can get is three columns, one of which seems to contain all the data within the nested columns (in a format I cannot correctly convert to a list).

The data structure in GBQ looks like this:

Field name	Type	Mode
id	STRING
date	STRING
*list	RECORD	REPEATED
*element	RECORD
name	STRING
output	BOOLEAN
scope	STRING

The output when trying to do a normal import looks something like this (the input in the third cell is abbreviated):

id	date	list
1	15/06/2021	{"v":[{"v":{"f":[{"v":{"f":[{"v":"Name_1"},{"v":"0"},{"v":"generic"}]}}]}},{"v":{"f":[{"v":{"f":[{"v":"Name_2"},{"v":"0"},{"v":"generic"}]}}]}}, (continues)
2	15/06/2021	{"v":[{"v":{"f":[{"v":{"f":[{"v":"Name_1"},{"v":"0"},{"v":"generic"}]}}]}},{"v":{"f":[{"v":{"f":[{"v":"Name_2"},{"v":"0"},{"v":"generic"}]}}]}}, (continues)

I've already tried to read the list column as a json, but nothing good came from it.

Also, PBI is not reading the list column as a list.

Any idea how to proceed?

Thanks!

[v-deddai1-msft]

Hi @Anonymous ,

Thank you for sharing the solution. Would you please try to accept your reply as answer to help others with the similar issue find it quickly.

Thank you for your sharing and understanding.

Best Regards,

Dedmon Dai

[Anonymous · Accepted solution]

For anybody who's having the same problem, here's how I managed to solve it.

The thing is, when you import nested tables from Google BigQuery, Power BI has trouble identifying the column names because the column names, when unnested, look something like this (in my case): list.element.name

To work around it, what I did was get the data through an ODBC query, in which I manually specified new column names for the columns that needed to be unnested. It looked something like this:

    SELECT
        id,

        date,
        unnested.element.rule_name AS rule_name,
        unnested.element.is_rejected AS is_rejected,
        unnested.element.rule_scope AS rule_scope
    FROM
        `my_data_location`,
    UNNEST(list) as unnested

[Namrata_Das_CLR]

Can you specify step by step the solution, i am facing the same issue, also my table is too large so have to apply  incremental refresh on this also.