5

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?

S.H
  • 671
  • 2
  • 6
  • 22

2 Answers2

5

Maybe I am late in the game, and perhaps I am not actually answering your question, but there is actually a setting where you can set how to display, or not to display, Null.

enter image description here

StupidWolf
  • 45,075
  • 17
  • 40
  • 72
Thomas
  • 61
  • 1
  • 1
  • Unfortunatly this does not OP's question, however it does point out correctly how to handle `null` values if you want them to not show up. Based on the title of the question, this might actually be what people look for, such as me. Thanks. – Mathias Sep 03 '21 at 20:52
4

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:

Nimantha
  • 6,405
  • 6
  • 28
  • 69