Data Studio obtain a transposed table from BigQuery and hidden conditional formatting in text/strings

Question

Several shops should be monitored about their status in a Data Studio dashboard.

There are less than 20 shops and I show here only two in the example. In the BigQuery table there is shop column and following columns: status, info, sold_today and update_time. The shop and update_time columns are always filled, but the other ones are filled only if there is a change.

Task: For each shop the last entries of all columns should be shown.

Here is the BigQuery code for the sample table:

create or replace table dsadds.eu.dummy as(
Select "shop A" as shop,  1000 as sold_today, "sale ABC" as info, 0 as status,timestamp( "2022-09-05") as update_time
union all select "shop A",null,null,1,"2022-09-06"
union all select "shop A" as shop,  500 as sold_today, "open" as status,3,"2022-09-01"
union all Select "shop B" as shop,  700 as sold_today, "open" as status,3,current_timestamp() as update_time
)

This table looks in Data Studio with conditional formatting, Status=1 marked red, like this:

As you can see the "Shop A" is shown several times and with null values. With following custom BigQuery in Data Studio I can obtain the last entry of each shop:

with tbl as 
(select shop,
array_agg(sold_today ignore nulls order by update_time desc limit 1)[safe_offset(0)] sold_today,
array_agg(info ignore nulls order by update_time desc limit 1)[safe_offset(0)] info,
array_agg(status ignore nulls order by update_time desc limit 1)[safe_offset(0)] status,

from dsadds.eu.dummy
group by 1
)
select * from tbl

resulting in following table, showing all needed information:

However, the users would like to have this table to be transposed and look like this:

-->

On the right hand side it is shown with the final textbox for the labeling of the rows.

Of course, it is possible to build for each entry a Scorecard, but with 10 shops and three field for each, the limit of charts per page was reached.

Question

Is there a way to transpose a table and also do the conditional formatting?

userG7m71 · Answer

The task is to return one column for each shop and a column id to sort the results. A column has to have one data type and for different rows, we cannot return one time a string and the other time a integer. Thus all integer values have to be formatted in BigQuery as strings.

For transpose, we build an tlb_array. The grouping by the shop generates an array for each one. The array has as first entry the shop name shop and as 2nd entry the column info and as 3rd entry we cast the sold_today column, which is an integer value to a string. We also include an id as entry number. By unnesting this array we unflatten the data and group it again by id in the next select statement. Here, we create a column for each shop and the if condition only considers data for this shop. Thus we end up with a table with three rows, with the row number in id. The needed data is in the shop columns.


with tbl as 
(select shop,
array_agg(sold_today ignore nulls order by update_time desc limit 1)[safe_offset(0)] sold_today,
array_agg(info ignore nulls order by update_time desc limit 1)[safe_offset(0)] info,
array_agg(status ignore nulls order by update_time desc limit 1)[safe_offset(0)] status,

from dsadds.eu.dummy
group by 1
),
tlb_array as (
Select shop,X.* from tbl, 
  unnest([
    struct(1 as id,shop as value),
    struct(2,info),
    struct(3,cast(sold_today as string))]) X
)
select id,
any_value(if(shop="shop A",value,null)) as ShopA,
any_value(if(shop="shop B",value,null)) as ShopB,

 from tlb_array
 group by 1

Thus we return only text via strings. But we want to apply conditional formatting without adding further columns. The trick is to include special characters in the returned string. ´ or ' is possible, but this would disturb the user. Therefore, the use of space characters is a good way. There are several unicode characters for different space distances. Thus a number can be encoded to space characters. Following UDF has to be hosted by you. It encodes each decimal digit of a number in a different unicode space character.

CREATE OR REPLACE FUNCTION `dsadds.us.number_to_space`(x INT64) AS (
(
    SELECT
      CONCAT(" ", 
        string_agg(SUBSTRING(
          CODE_POINTS_TO_STRING([0x2007, 0x2002, 0x2004, 0x2005, 0x2006, 0x2008, 0x2009, 0x200A, 0x202F, 0x205F]),
          y-47,1),"")
        ,"- ")
    FROM
      UNNEST(TO_CODE_POINTS(CAST(x AS string))) y )
);

Then you can use this function in your Custom BigQuery in Data Studio:

 
with tbl as 
(select shop,
array_agg(sold_today ignore nulls order by update_time desc limit 1)[safe_offset(0)] sold_today,
array_agg(info ignore nulls order by update_time desc limit 1)[safe_offset(0)] info,
array_agg(status ignore nulls order by update_time desc limit 1)[safe_offset(0)] status,

from dsadds.us.dummy
group by 1
),
tlb_array as (
Select shop,X.* from tbl, 
  unnest([
    struct(1 as id,concat(shop,dsadds.us.number_to_space(status)) as value),
    struct(2,concat(info,dsadds.us.number_to_space(status))),
    struct(3,cast(sold_today as string))]) X
)
select id,
any_value(if(shop="shop A",value,null)) as ShopA,
any_value(if(shop="shop B",value,null)) as ShopB,

 from tlb_array
 group by 1

This will result in following needed table. The (hidden) space characters have to be copied from the table (only in view not in edit mode of Data Studio possible) and condition formatting rules added (text contrains: ). Please also adding a textbox over the first column to hide it and enter the labels for each row.

Data Studio obtain a transposed table from BigQuery and hidden conditional formatting in text/strings

Question

1 Answers