Transform a single row value into replicated column across multiple rows using Grafana transforms

Grafana
,
1

Context

Using Grafana 11 with a data table containing:

  • One “Runtime” row with total machine operating duration

  • Multiple rows for each failure cause with their occurrence count

Current Data Structure

Field          | Value
---------------|-------
runtime        | 279132
Panne elec     | 80
Panne moteur   | 33
Panne pompe    | 18

Desired Output

Transform this data only with “tranforms” into tabular format to create a bar chart with MTBF Mean Time Between Failure (runtime duration/occurence by failure) :

Cause        | Occurrence | Runtime | MTBF
-------------|------------|---------|-------
Panne elec   | 80         | 279132  | 3489
Panne moteur | 33         | 279132  | 8459
Panne pompe  | 18         | 279132  | 15507

Technical Constraints

  • Must use only Grafana transforms (no source query modification)

  • Solution must be dynamic to accommodate variable number of failure causes

  • Grafana version 11

2

so you do mention that it is a single row but the data you provided is multi row


Field          | Value
---------------|-------
runtime        | 279132
Panne elec     | 80
Panne moteur   | 33
Panne pompe    | 18

Does the data in the datasource look like this instead?

runtime,Panne elec,Panne moteur,Panne pompe
279132,80,33,18

This would help us emulate the original data to see what can be done.

3

Yes sorry, I will give you better explaination :

Actually, I don’t start with a single row in my datasource. I have two separate queries from my datasource:

  1. Query “states duration” - Returns the runtime duration:
runtime
279132
  1. Query “states occurrences” - Returns multiple tables for each failure cause as occurrences:
Panne elec
80

Panne moteur  
33

Panne pompe
18

The multi-row format I showed earlier is the result of processing these two queries through several Grafana transforms:

  • Original

image
image1489×616 27.4 KB

  • Join by field (to combine both queries)

image
image1513×265 20.9 KB

  • Reduce (to consolidate the data)

image
image1507×828 43.6 KB

  • Transpose (to get the current format)

image
image1518×535 31 KB

So the table I’m working with:

Field          | Value
---------------|-------
runtime        | 279132
Panne elec     | 80
Panne moteur   | 33
Panne pompe    | 18

Is already a transformed result, not the original datasource format. My issue is that I need to replicate that single “runtime” value (279132) as a column across all the failure cause rows, but I can’t easily do this with the current Grafana transform options.

1 Like