Search code examples
nullfieldlooker-studio

Google Data Studio displays "null" – how to set the field value?

I am new to Google Data Studio. I think I have an issue, with turning dimensions into metrics or with correct assignement of values. This is because a colulmn which should display numbers just displays the string „null“.

This is how it should look. (Example from book) It should look like this with "real" numbers in the transaction amount column

But this is how it looks:

As you can see, transaction number is "0". And in the Data Control Panel on the right, "Balance" and "Transaction Amount" is a dimension (green) and not a metric – in the "Availabe Fields" panel

The column „Transaction Number“ is „null“.

I think sth. went wrong with the data source. It’s an CSV. Like this: This is the data-strcuture in the csv

And within Data Studio I transformed some of the field values and the Aggregation.

I don't get the fields like in the book. So for example "Balance" is a dimension – and not a metric. But I have the some type as in the book

I did not get Transaction amount blue colored, like it should be, (see example from book below) And also „balance“ is a not blue (metric.) enter image description here

I think, this leads to displaying transaction amount column to „null“. But I can’t turn these values into metrics from within the data control panel.

But maybe I am wrong. So what need Datas Studio to display actual values and not null?

Solution

  • Summary

    The number columns (Transaction Amount and Balance) are currently treated as Text columns in Google Sheets (one indication is the left alignment of the respective values, which is the default for Plain Text columns in Google Sheets), thus the symbols $ and , are part of the value thus $200,000.00 is treated a literal and is displayed as a Text field when creating the Google Data Studio Data Source.

    One approach is to change the formatting in Google Sheets (or remove the respective characters $ and ,); the below looks at a suggestion within Google Data Studio by extracting the numbers from the field, using three steps:

    1) Text

    At the Data Source, ensure that both the Transaction Amount and Balance fields are set to Text fields.

    A GIF to elaborate:

    2) Calculated Fields

    At the Data Source, create the two REGEXP_REPLACE Calculated Fields below and set the semantic type to Currency (USD):

    2.1) Transaction Amount

    CAST(REGEXP_REPLACE(REGEXP_REPLACE(Transaction Amount, "\\$", ""), ",", "") AS NUMBER )

    2.2) Balance

    CAST(REGEXP_REPLACE(REGEXP_REPLACE(Balance, "\\$", ""), ",", "") AS NUMBER )

    2.3) Hide
    Hide the original Text fields for Transaction Amount and Balance

    GIF to visualise the above:

    3) Metrics

    Add the Newly created Transaction Amount and Balance Currency Fields into the required chart, such as Metrics in a Table.

    Google Data Studio Report and a GIF to elaborate: