cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martifapa
Frequent Visitor

Import a nested table from Google Big Query

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 nameTypeMode
idSTRING 
dateSTRING 
*listRECORDREPEATED
       *elementRECORD 
              nameSTRING 
              outputBOOLEAN 
              scopeSTRING 

 

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

iddatelist
115/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)
215/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!

1 ACCEPTED SOLUTION
martifapa
Frequent Visitor

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

View solution in original post

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

Hi @martifapa ,

 

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

martifapa
Frequent Visitor

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

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors