Google Data Studio recently introduced new join operations additional to the left outer join, the full outer join being one of them. A full outer join can obviously often lead to null values in the blended table.

If I have the following table (null values represented by -) that results from a blend with a full outer join, with the join on

• Company Table 1 <-> Company Table 2
• Month Table 1 <-> Month Table 2

If I now create a pivot table from that blended data and I use Company Table 1 as row dimension and Month Table 1 as column dimension and NARY_MAX(Amount Table 1,0) - NARY_MAX(Amount Table 2,0) as the metric, I get get the following

This is quite understandable, but my question is, if I could somehow tell Data Studio to use the dimensions Company Table 2 and Month Table 2 as dimensions if the ones from Table 1 are null.

The desired outcome would be

Here you can finde the above data in a publicly accessible and editable example

Does anyone have an idea on how to accomplish this?

CodePudding user response：

The COALESCE function ("returns the first non-missing value found in a list of fields") can be used to deal with the NULL values in both the Company and Month fields:

1) Company COALESCE

• COALESCE(Company (Table 1), Company (Table 2))
  

• Type: Text

2) Month COALESCE

• COALESCE(Month (Table 1), Month (Table 2))
  

• Type: Date > Year Month

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate: