Need help with Table Transformations

[vt6109]

Hello Grafana Community,

I need some help with transforming my data into the correct form in a Grafana Table panel.
Various hours of web searching resulted in only confusion on how this should best be done.
I also used Grafana’s Grot AI helper | Grafana Labs and chatgpt, but both give me “solutions” that use non existing transformations and cause me more confusion.

What does my data look like
I have sql data that looks like the data in the following table:

Location	Device	Measurement Type	Value	Unit	Status
Location2	Device1	Ph Level	6	(no unit)	ok
Location2	Device2	Water Temperature	30	°C	ok
Location2	Device1	Air Temperature	25	°C	alarm
Location1	Device1	Air Temperature	22	°C	ok
Location1	Device1	Water Chlorine Concentration	10	ppm	warning
Location1	Device2	Air Temperature	10	°C	alarm

Short summary on the data: It contains different locations throughout the city, that have multiple devices monitored, for multiple measurements (such as air temp, water temp, water chlorine level, …).
Locations can have multiple Devices. Devices can have multiple types of measurement.

What do i want my result to look like

• The “Location” values should become column headers
• The row headers should be defined by “Device” and “Type Of Measurement”
• The cell values should contain the “Value” (if a value exists for the combination of the row/column)
• The cell should have it’s “unit” (for example °C) defined by the “unit” value from the data
• The cell should have its color defined by the “Status” value (for example, red if alarm, orange if warning, green if ok)

The resulting table should look something like this:

Column 1	Location1	Location2
Device 1 - Air Temperature	22°C	25°C
Device 1 - Ph Level		6
Device 1 - Water Chlorine Concentration	10
Device 2 - Air Temperature	10°C
Device 2 - Water Temperature		30°C

Question
How can i achieve what i have described here?
Any input is appreciated.

Thanks in advance for taking the time to help me!
Kind regards,
Wesley

[sowdenraymond]

it’s kinda painful, but lets go!

i used two transformations to concatenate the “device and measurement” and the “value and unit”:

please note these new fields are number types, as they have had arithmetic applied. i then grouped them and organized them:

then partition the data and join it back together, sort of like pivoting it in excel:

organize the fields to rename the columns, convert the column to a string, coz it was numeric from above, and then sort it

you can play with value mappings and regex to make device1 device 1 and add in the hyphens, and hide the (no unit) etc.

[vt6109]

Hi Raymond,

First of all, love the confidence haha!
Secondly: Thanks for the quick and clear instructions on how to solve this issue! You clearly know your Grafana stuff

Just one more question:
How can i map the “Status” to a color of the cell/text?
For example if status is “ok” then the cell should be green.
Is this also possible?
Could you give me some instructions on how to do this, or point me in the right direction?

Thanks in advance,
Kind regards,
Wesley

[sowdenraymond]

add status to the group by, add some value mappings (make sure your text matches exactly, including leading or trailing spaces if necessary), and add some field overrides to make the cells coloured backgrounds: