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

Question

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)

But this is how it looks:

The column „Transaction Number“ is „null“.

I think sth. went wrong with the data source. It’s an CSV. Like this:

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

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.)

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?

Answer 1

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: