Issue displaying empty value of repeated columns in Google Data Studio

user_13554692 0
Viewed 987

I've got an issue when trying to visualize in Google Data Studio some information from a denormalized table.

Context: I want to gather all the contact of a company and there related orders in a table in Big Query. Contacts can have no order or multiple orders. Following Big Query best practice, this table is denormalized and all the orders for a client are in arrays of struct. It looks like this:

Fields Examples:

+-------+------------+-------------+-----------+
| Row # | Contact_Id | Orders.date | Orders.id |
+-------+------------+-------------+-----------+
|-  1   | 23         | 2019-02-05  | CB1       |
|       |            | 2020-03-02  | CB293     |
|-  2   | 2321       |   -         |   -       |
|-  3   | 77         | 2010-09-03  | AX3       |
+-------+------------+-------------+-----------+

The issue is when I want to use this table as a data source in Data Studio. For instance, if I build a table with Contact_Id as dimension, everything is fine and I can see all my contacts. However, if I add any dimensions from the Orders struct, all info from contact with no orders are not displayed. For instance, all info from Contact_Id 2321 is removed from the table.

Have you find any workaround to visualize these empty arrays (for instance as null values)?
The only solution I've found is to build an intermediary table with the orders unnested.

google-data-studio google-bigquery
2 Answers

The way I've just discovered to work around this is to add an extra field in my DS-> BQ connector:

ARRAY_LENGTH(fields.orders) AS numberoforders

This will return zero if the array is empty - you can then create calculated fields within DataStudio - using the "numberoforders" field to force values to NULL or zero.

user_169927330

You can fix this behaviour by changing a little your query on the BigQuery connector.

Instead of doing this:

SELECT
    Contact_id,
    Orders
FROM myproject.mydataset.mytable

try this:

SELECT
    Contact_id,
    IF(ARRAY_LENGTH(Orders) > 0, Orders, [STRUCT(CAST(NULL AS DATE) AS date, CAST(NULL AS STRING) AS id)]) AS Orders
FROM myproject.mydataset.mytable

This way you are forcing your repeated field to have, at least, an array containing NULL values and hence Data Studio will represent those missing values.

Also, if you want to create new calculated fields using one of the nested fields, you should check before if the value is NULL to avoid filling all NULL values. For example, if you have a repeated and nested field which can be 1 or 0, and you want to create a calculated field swaping the value, you should do:

IF(myfield.key IS NOT NULL, IF(myfield.key = 1, 0, 1), NULL)

Here you can see what happens if you check before swaping and if you don't:

Original value        No check        Check
1                     0               0
0                     1               1
NULL                  1               NULL
1                     0               0
NULL                  1               NULL
user_82751390
Related