Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to 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
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
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.
User | Count |
---|---|
84 | |
76 | |
74 | |
48 | |
39 |
User | Count |
---|---|
114 | |
56 | |
51 | |
42 | |
42 |