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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.