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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

3 REPLIES 3
v-deddai1-msft
Community Support
Community Support

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
Not applicable

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

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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