This article shows a method to create a transposed table with a standard Looker results table. Beginning in Looker 6.14, transpose table is a visualization option for the Table Next labs feature. For more information, see this documentation page.
The Goal
Suppose you want to transpose your table, which means showing the measures as rows in your Explore page. For example, if your data looks like this:
The goal would be to show all the different Count measures grouped together, rather than all the Years grouped together. This is possible, by using dimension fill to manufacture some rows, and then using a table calculations to fill in those manufactured rows.
The finished product will look like this:
The Process
Step 1: Case Dimension
Make a dummy case
dimension, with as many cases as there are measures.
In this example:
dimension: dummy_three {
case: {
when: {
label: "Count"
sql: 1=1 ;;
}
when: {
label: "Count Inventory Items"
sql: 1=1 ;;
}
when: {
label: "Count Orders"
sql: 1=1 ;;
}
}
}
Step 2: Pivot and Dimension Fill
Pivot on your original dimension, include the dummy dimension, and then dimension fill the dummy. Here, we are taking advantage of the fact that case
statements can be filled, even if they do not have data.
This step will look like this—all the data will be in the first row for now:
Step 3: Make a Table Calculation
Now, we need to make a table calculation that gets the data out of that first row and into the second and third rows. The calculation here checks the row number and inserts the proper measure value into that row.
coalesce(
if(row()=1, ${order_items.count}, null),
if(row()=2, index(${order_items.count_orders}, 1), null),
if(row()=3, index(${order_items.count_inventory_items}, 1), null),
null)
Step 4: Touch up Visualization
Finally, hide the measures. The table calculation should have the only values you need.
That's it! Now you have a transpose table in your Explore page.