Stacked bar chart for azure finops

Hi
Im doing some finops analytics from Azure !
Im using infinity datasource !

I want to show price by day with stacked value of each service

exemple:
image

For the moment my result look like this !


I have the total of the day but no stacked values !

Im using those transformations :

  "transformations": [
    {
      "id": "extractFields",
      "options": {
        "replace": true,
        "source": "A"
      }
    },
    {
      "id": "organize",
      "options": {
        "excludeByName": {},
        "indexByName": {
          "0": 0,
          "1": 1,
          "2": 2,
          "3": 3
        },
        "renameByName": {
          "0": "Cost",
          "1": "Date",
          "2": "ServiceName",
          "3": "Currency"
        },
        "includeByName": {}
      }
    },
    {
      "id": "groupBy",
      "options": {
        "fields": {
          "Cost": {
            "aggregations": [
              "sum"
            ],
            "operation": "aggregate"
          },
          "Date": {
            "aggregations": [],
            "operation": "groupby"
          },
          "ServiceName": {
            "aggregations": [
              "allValues"
            ],
            "operation": "aggregate"
          }
        }
      }
    }
  ]

one data row look like this :

[1.2,20241001,"VM","EUR"]

I also want to make the date look like this
2024-10-09 insted of 20241009

Thanks :smiley:

In case you want the ful pannel json

{
  "datasource": {
    "default": false,
    "uid": "ee0fp0b2eyqdcb",
    "type": "yesoreyeram-infinity-datasource"
  },
  "fieldConfig": {
    "defaults": {
      "custom": {
        "lineWidth": 1,
        "fillOpacity": 80,
        "gradientMode": "none",
        "axisPlacement": "auto",
        "axisLabel": "",
        "axisColorMode": "text",
        "axisBorderShow": false,
        "scaleDistribution": {
          "type": "linear"
        },
        "axisCenteredZero": false,
        "hideFrom": {
          "tooltip": false,
          "viz": false,
          "legend": false
        },
        "thresholdsStyle": {
          "mode": "line"
        }
      },
      "color": {
        "mode": "thresholds"
      },
      "mappings": [],
      "thresholds": {
        "mode": "percentage",
        "steps": [
          {
            "color": "green",
            "value": null
          }
        ]
      }
    },
    "overrides": []
  },
  "gridPos": {
    "h": 9,
    "w": 24,
    "x": 0,
    "y": 1
  },
  "id": 5,
  "options": {
    "orientation": "vertical",
    "xTickLabelRotation": 0,
    "xTickLabelSpacing": 0,
    "showValue": "always",
    "stacking": "none",
    "groupWidth": 0.7,
    "barWidth": 0.75,
    "barRadius": 0,
    "fullHighlight": false,
    "tooltip": {
      "mode": "multi",
      "sort": "none"
    },
    "legend": {
      "showLegend": true,
      "displayMode": "list",
      "placement": "bottom",
      "calcs": []
    },
    "colorByField": "Cost",
    "xField": "Date"
  },
  "pluginVersion": "11.2.2",
  "targets": [
    {
      "datasource": {
        "type": "yesoreyeram-infinity-datasource",
        "uid": "ee0fp0b2eyqdcb"
      },
      "columns": [],
      "filters": [],
      "format": "table",
      "global_query_id": "",
      "parser": "backend",
      "refId": "A",
      "root_selector": "properties.rows[*]",
      "source": "url",
      "type": "json",
      "url": "https://management.azure.com/subscriptions/e8134c01-8d85-4322-b67b-0d4259dd8fdc/providers/Microsoft.CostManagement/query?api-version=2023-11-01",
      "url_options": {
        "body_content_type": "application/json",
        "body_type": "raw",
        "data": "{\n  \"type\": \"Usage\",\n  \"timeframe\": \"Custom\",\n  \"timePeriod\": {\n    \"from\": \"$from\",\n    \"to\": \"$to\"\n  },\n  \"dataset\": {\n    \"granularity\": \"Daily\",\n    \"aggregation\": {\n      \"totalCost\": {\n        \"name\": \"Cost\",\n        \"function\": \"Sum\"\n      }\n    },\n    \"grouping\": [\n      {\n        \"type\": \"Dimension\",\n        \"name\": \"ServiceName\"\n      }\n    ]\n  }\n}",
        "method": "POST"
      }
    }
  ],
  "title": "Usage Cost - Applied Duration",
  "transformations": [
    {
      "id": "extractFields",
      "options": {
        "replace": true,
        "source": "A"
      }
    },
    {
      "id": "organize",
      "options": {
        "excludeByName": {},
        "indexByName": {
          "0": 0,
          "1": 1,
          "2": 2,
          "3": 3
        },
        "renameByName": {
          "0": "Cost",
          "1": "Date",
          "2": "ServiceName",
          "3": "Currency"
        },
        "includeByName": {}
      }
    },
    {
      "id": "groupBy",
      "options": {
        "fields": {
          "Cost": {
            "aggregations": [
              "sum"
            ],
            "operation": "aggregate"
          },
          "Date": {
            "aggregations": [],
            "operation": "groupby"
          },
          "ServiceName": {
            "aggregations": [
              "allValues"
            ],
            "operation": "aggregate"
          }
        }
      }
    }
  ],
  "type": "barchart"
}

Choose time series as Format and also Stacking Normal

image


I just tried but it dont seem to work !

My data tranform look like this

please post raw json data coming from your rest api or endpoint

                      "rows": [
                        [
                          9.25597434792304,
                          20241001,
                          "Application Gateway",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Automation",
                          "EUR"
                        ],
                        [
                          2.88339078296502,
                          20241001,
                          "NAT Gateway",
                          "EUR"
                        ],
                        [
                          11.2886013719307,
                          20241002,
                          "Application Gateway",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Automation",
                          "EUR"
                        ],
                        [
                          0.384953682024142,
                          20241002,
                          "Azure DNS",
                          "EUR"
                      ]

Thats an example of the source (i cant put all the json there is private data)
It correspond to the array that i get from parsing my output

1 Like

so that is not a valid json. please vet data

{
  "request": {
    "url": "api/ds/query?ds_type=yesoreyeram-infinity-datasource&requestId=Q138",
    "method": "POST",
    "data": {
      "queries": [
        {
          "datasource": {
            "type": "yesoreyeram-infinity-datasource",
            "uid": "ee0fp0b2eyqdcb"
          },
          "columns": [],
          "filters": [],
          "format": "timeseries",
          "global_query_id": "",
          "parser": "backend",
          "refId": "A",
          "root_selector": "properties.rows[*]",
          "source": "url",
          "type": "json",
          "url": "https://management.azure.com/subscriptions//providers/Microsoft.CostManagement/query?api-version=2023-11-01",
          "url_options": {
            "body_content_type": "application/json",
            "body_type": "raw",
            "data": "{\n  \"type\": \"Usage\",\n  \"timeframe\": \"Custom\",\n  \"timePeriod\": {\n    \"from\": \"2024-10-01\",\n    \"to\": \"2024-10-02\"\n  },\n  \"dataset\": {\n    \"granularity\": \"Daily\",\n    \"aggregation\": {\n      \"totalCost\": {\n        \"name\": \"Cost\",\n        \"function\": \"Sum\"\n      }\n    },\n    \"grouping\": [\n      {\n        \"type\": \"Dimension\",\n        \"name\": \"ServiceName\"\n      }\n    ]\n  }\n}",
            "method": "POST",
            "body_graphql_query": "",
            "body_graphql_variables": ""
          },
          "computed_columns": [],
          "filterExpression": "",
          "summarizeExpression": "",
          "datasourceId": 3,
          "intervalMs": 30000,
          "maxDataPoints": 719
        }
      ],
      "from": "1728561381961",
      "to": "1728582981961"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "status": 200,
        "frames": [
          {
            "schema": {
              "name": "A",
              "refId": "A",
              "meta": {
                "typeVersion": [
                  0,
                  0
                ],
                "custom": {
                  "data": {
                    "eTag": null,
                    "id": "subscriptions//providers/Microsoft.CostManagement/query/",
                    "location": null,
                    "name": "",
                    "properties": {
                      "columns": [
                        {
                          "name": "Cost",
                          "type": "Number"
                        },
                        {
                          "name": "UsageDate",
                          "type": "Number"
                        },
                        {
                          "name": "ServiceName",
                          "type": "String"
                        },
                        {
                          "name": "Currency",
                          "type": "String"
                        }
                      ],
                      "nextLink": null,
                      "rows": [
                        [
                          9.25597434792304,
                          20241001,
                          "Application Gateway",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Automation",
                          "EUR"
                        ],
                        [
                          16.2412401575407,
                          20241001,
                          "Azure App Service",
                          "EUR"
                        ],
                        [
                          6.23292884968432,
                          20241001,
                          "Azure Bastion",
                          "EUR"
                        ],
                        [
                          0.430322005180519,
                          20241001,
                          "Azure DNS",
                          "EUR"
                        ],
                        [
                          0.00000895535754264989,
                          20241001,
                          "Azure Data Factory v2",
                          "EUR"
                        ],
                        [
                          5.95997228413135,
                          20241001,
                          "Azure Database for MySQL",
                          "EUR"
                        ],
                        [
                          0.983587140684334,
                          20241001,
                          "Azure Database for PostgreSQL",
                          "EUR"
                        ],
                        [
                          10.601987800492,
                          20241001,
                          "Azure DevOps",
                          "EUR"
                        ],
                        [
                          24.5640844304826,
                          20241001,
                          "Azure Front Door Service",
                          "EUR"
                        ],
                        [
                          4.87307059365934,
                          20241001,
                          "Backup",
                          "EUR"
                        ],
                        [
                          0.00722409149740619,
                          20241001,
                          "Bandwidth",
                          "EUR"
                        ],
                        [
                          1.19393781901222,
                          20241001,
                          "Container Registry",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Insight and Analytics",
                          "EUR"
                        ],
                        [
                          1.61199901664664,
                          20241001,
                          "Load Balancer",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Log Analytics",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Microsoft Defender for Cloud",
                          "EUR"
                        ],
                        [
                          2.88339078296502,
                          20241001,
                          "NAT Gateway",
                          "EUR"
                        ],
                        [
                          3.71826445170823,
                          20241001,
                          "Redis Cache",
                          "EUR"
                        ],
                        [
                          6.30446578713115,
                          20241001,
                          "Storage",
                          "EUR"
                        ],
                        [
                          14.9138982492276,
                          20241001,
                          "Virtual Machines",
                          "EUR"
                        ],
                        [
                          5.0938043712376,
                          20241001,
                          "Virtual Network",
                          "EUR"
                        ],
                        [
                          11.2886013719307,
                          20241002,
                          "Application Gateway",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Automation",
                          "EUR"
                        ],
                        [
                          16.2412401575407,
                          20241002,
                          "Azure App Service",
                          "EUR"
                        ],
                        [
                          6.23292884968432,
                          20241002,
                          "Azure Bastion",
                          "EUR"
                        ],
                        [
                          0.384953682024142,
                          20241002,
                          "Azure DNS",
                          "EUR"
                        ],
                        [
                          5.52003252094759,
                          20241002,
                          "Azure Database for MySQL",
                          "EUR"
                        ],
                        [
                          0.983587140684334,
                          20241002,
                          "Azure Database for PostgreSQL",
                          "EUR"
                        ],
                        [
                          10.601987800492,
                          20241002,
                          "Azure DevOps",
                          "EUR"
                        ],
                        [
                          23.4248006876139,
                          20241002,
                          "Azure Front Door Service",
                          "EUR"
                        ],
                        [
                          4.86812372899352,
                          20241002,
                          "Backup",
                          "EUR"
                        ],
                        [
                          0.0082495189270704,
                          20241002,
                          "Bandwidth",
                          "EUR"
                        ],
                        [
                          1.19393781901222,
                          20241002,
                          "Container Registry",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Insight and Analytics",
                          "EUR"
                        ],
                        [
                          1.611990853039,
                          20241002,
                          "Load Balancer",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Log Analytics",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Microsoft Defender for Cloud",
                          "EUR"
                        ],
                        [
                          3.01820150195504,
                          20241002,
                          "NAT Gateway",
                          "EUR"
                        ],
                        [
                          3.71826445170823,
                          20241002,
                          "Redis Cache",
                          "EUR"
                        ],
                        [
                          6.34559069977164,
                          20241002,
                          "Storage",
                          "EUR"
                        ],
                        [
                          14.9934205174406,
                          20241002,
                          "Virtual Machines",
                          "EUR"
                        ],
                        [
                          5.18118969008666,
                          20241002,
                          "Virtual Network",
                          "EUR"
                        ]
                      ]
                    },
                    "sku": null,
                    "type": "Microsoft.CostManagement/query"
                  },
                  "duration": 830837230,
                  "error": "",
                  "query": {
                    "alias": "",
                    "columns": [],
                    "computed_columns": [],
                    "csv_options": {
                      "columns": "",
                      "comment": "",
                      "delimiter": "",
                      "relax_column_count": false,
                      "skip_empty_lines": false,
                      "skip_lines_with_error": false
                    },
                    "data": "",
                    "dataOverrides": null,
                    "expression": "",
                    "filterExpression": "",
                    "filters": [],
                    "format": "timeseries",
                    "global_query_id": "",
                    "groq": "",
                    "json_options": {
                      "columnar": false,
                      "root_is_not_array": false
                    },
                    "parser": "backend",
                    "query_mode": "",
                    "refId": "A",
                    "root_selector": "properties.rows[*]",
                    "seriesCount": 0,
                    "source": "url",
                    "summarizeAlias": "",
                    "summarizeBy": "",
                    "summarizeExpression": "",
                    "type": "json",
                    "uql": "",
                    "url": "https://management.azure.com/subscriptions//providers/Microsoft.CostManagement/query?api-version=2023-11-01",
                    "url_options": {
                      "body_content_type": "application/json",
                      "body_form": null,
                      "body_graphql_query": "",
                      "body_graphql_variables": "",
                      "body_type": "raw",
                      "data": "{\n  \"type\": \"Usage\",\n  \"timeframe\": \"Custom\",\n  \"timePeriod\": {\n    \"from\": \"2024-10-01\",\n    \"to\": \"2024-10-02\"\n  },\n  \"dataset\": {\n    \"granularity\": \"Daily\",\n    \"aggregation\": {\n      \"totalCost\": {\n        \"name\": \"Cost\",\n        \"function\": \"Sum\"\n      }\n    },\n    \"grouping\": [\n      {\n        \"type\": \"Dimension\",\n        \"name\": \"ServiceName\"\n      }\n    ]\n  }\n}",
                      "headers": null,
                      "method": "POST",
                      "params": null
                    }
                  },
                  "responseCodeFromServer": 200
                },
                "executedQueryString": "###############\n## URL\n###############\n\nhttps://management.azure.com/subscriptions//providers/Microsoft.CostManagement/query?api-version=2023-11-01\n\n###############\n## Curl Command\n###############\n\ncurl -X 'POST' -d '{\n  \"type\": \"Usage\",\n  \"timeframe\": \"Custom\",\n  \"timePeriod\": {\n    \"from\": \"2024-10-01\",\n    \"to\": \"2024-10-02\"\n  },\n  \"dataset\": {\n    \"granularity\": \"Daily\",\n    \"aggregation\": {\n      \"totalCost\": {\n        \"name\": \"Cost\",\n        \"function\": \"Sum\"\n      }\n    },\n    \"grouping\": [\n      {\n        \"type\": \"Dimension\",\n        \"name\": \"ServiceName\"\n      }\n    ]\n  }\n}' -H 'Accept: application/json;q=0.9,text/plain' -H 'Content-Type: application/json' 'https://management.azure.com/subscriptions//providers/Microsoft.CostManagement/query?api-version=2023-11-01'\n###############\n> Authentication steps not included for OAuth authentication"
              },
              "fields": [
                {
                  "name": "A",
                  "type": "string",
                  "typeInfo": {
                    "frame": "string",
                    "nullable": true
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  "[9.25597434792304,20241001,\"Application Gateway\",\"EUR\"]",
                  "[0,20241001,\"Automation\",\"EUR\"]",
                  "[16.2412401575407,20241001,\"Azure App Service\",\"EUR\"]",
                  "[6.23292884968432,20241001,\"Azure Bastion\",\"EUR\"]",
                  "[0.430322005180519,20241001,\"Azure DNS\",\"EUR\"]",
                  "[0.00000895535754264989,20241001,\"Azure Data Factory v2\",\"EUR\"]",
                  "[5.95997228413135,20241001,\"Azure Database for MySQL\",\"EUR\"]",
                  "[0.983587140684334,20241001,\"Azure Database for PostgreSQL\",\"EUR\"]",
                  "[10.601987800492,20241001,\"Azure DevOps\",\"EUR\"]",
                  "[24.5640844304826,20241001,\"Azure Front Door Service\",\"EUR\"]",
                  "[4.87307059365934,20241001,\"Backup\",\"EUR\"]",
                  "[0.00722409149740619,20241001,\"Bandwidth\",\"EUR\"]",
                  "[1.19393781901222,20241001,\"Container Registry\",\"EUR\"]",
                  "[0,20241001,\"Insight and Analytics\",\"EUR\"]",
                  "[1.61199901664664,20241001,\"Load Balancer\",\"EUR\"]",
                  "[0,20241001,\"Log Analytics\",\"EUR\"]",
                  "[0,20241001,\"Microsoft Defender for Cloud\",\"EUR\"]",
                  "[2.88339078296502,20241001,\"NAT Gateway\",\"EUR\"]",
                  "[3.71826445170823,20241001,\"Redis Cache\",\"EUR\"]",
                  "[6.30446578713115,20241001,\"Storage\",\"EUR\"]",
                  "[14.9138982492276,20241001,\"Virtual Machines\",\"EUR\"]",
                  "[5.0938043712376,20241001,\"Virtual Network\",\"EUR\"]",
                  "[11.2886013719307,20241002,\"Application Gateway\",\"EUR\"]",
                  "[0,20241002,\"Automation\",\"EUR\"]",
                  "[16.2412401575407,20241002,\"Azure App Service\",\"EUR\"]",
                  "[6.23292884968432,20241002,\"Azure Bastion\",\"EUR\"]",
                  "[0.384953682024142,20241002,\"Azure DNS\",\"EUR\"]",
                  "[5.52003252094759,20241002,\"Azure Database for MySQL\",\"EUR\"]",
                  "[0.983587140684334,20241002,\"Azure Database for PostgreSQL\",\"EUR\"]",
                  "[10.601987800492,20241002,\"Azure DevOps\",\"EUR\"]",
                  "[23.4248006876139,20241002,\"Azure Front Door Service\",\"EUR\"]",
                  "[4.86812372899352,20241002,\"Backup\",\"EUR\"]",
                  "[0.0082495189270704,20241002,\"Bandwidth\",\"EUR\"]",
                  "[1.19393781901222,20241002,\"Container Registry\",\"EUR\"]",
                  "[0,20241002,\"Insight and Analytics\",\"EUR\"]",
                  "[1.611990853039,20241002,\"Load Balancer\",\"EUR\"]",
                  "[0,20241002,\"Log Analytics\",\"EUR\"]",
                  "[0,20241002,\"Microsoft Defender for Cloud\",\"EUR\"]",
                  "[3.01820150195504,20241002,\"NAT Gateway\",\"EUR\"]",
                  "[3.71826445170823,20241002,\"Redis Cache\",\"EUR\"]",
                  "[6.34559069977164,20241002,\"Storage\",\"EUR\"]",
                  "[14.9934205174406,20241002,\"Virtual Machines\",\"EUR\"]",
                  "[5.18118969008666,20241002,\"Virtual Network\",\"EUR\"]"
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}
1 Like

one possible way is using jsonata

https://try.jsonata.org/xWHG3dJTH

I just tryied like you :

Json data:

[
  [
    9.25597434792304,
    20241001,
    "Application Gateway",
    "EUR"
  ],
  [
    0,
    20241001,
    "Automation",
    "EUR"
  ],
  [
    16.2412401575407,
    20241001,
    "Azure App Service",
    "EUR"
  ],
  [
    6.23292884968432,
    20241001,
    "Azure Bastion",
    "EUR"
  ],
  [
    0.430322005180519,
    20241001,
    "Azure DNS",
    "EUR"
  ],
  [
    0.00000895535754264989,
    20241001,
    "Azure Data Factory v2",
    "EUR"
  ],
  [
    5.95997228413135,
    20241001,
    "Azure Database for MySQL",
    "EUR"
  ],
  [
    0.983587140684334,
    20241001,
    "Azure Database for PostgreSQL",
    "EUR"
  ],
  [
    10.601987800492,
    20241001,
    "Azure DevOps",
    "EUR"
  ],
  [
    24.5640844304826,
    20241001,
    "Azure Front Door Service",
    "EUR"
  ],
  [
    4.87307059365934,
    20241001,
    "Backup",
    "EUR"
  ],
  [
    0.00722409149740619,
    20241001,
    "Bandwidth",
    "EUR"
  ],
  [
    1.19393781901222,
    20241001,
    "Container Registry",
    "EUR"
  ],
  [
    0,
    20241001,
    "Insight and Analytics",
    "EUR"
  ],
  [
    1.61199901664664,
    20241001,
    "Load Balancer",
    "EUR"
  ],
  [
    0,
    20241001,
    "Log Analytics",
    "EUR"
  ],
  [
    0,
    20241001,
    "Microsoft Defender for Cloud",
    "EUR"
  ],
  [
    2.88339078296502,
    20241001,
    "NAT Gateway",
    "EUR"
  ],
  [
    3.71826445170823,
    20241001,
    "Redis Cache",
    "EUR"
  ],
  [
    6.30446578713115,
    20241001,
    "Storage",
    "EUR"
  ],
  [
    14.9138982492276,
    20241001,
    "Virtual Machines",
    "EUR"
  ],
  [
    5.0938043712376,
    20241001,
    "Virtual Network",
    "EUR"
  ],
  [
    11.2886013719307,
    20241002,
    "Application Gateway",
    "EUR"
  ],
  [
    0,
    20241002,
    "Automation",
    "EUR"
  ],
  [
    16.2412401575407,
    20241002,
    "Azure App Service",
    "EUR"
  ],
  [
    6.23292884968432,
    20241002,
    "Azure Bastion",
    "EUR"
  ],
  [
    0.384953682024142,
    20241002,
    "Azure DNS",
    "EUR"
  ],
  [
    5.52003252094759,
    20241002,
    "Azure Database for MySQL",
    "EUR"
  ],
  [
    0.983587140684334,
    20241002,
    "Azure Database for PostgreSQL",
    "EUR"
  ],
  [
    10.601987800492,
    20241002,
    "Azure DevOps",
    "EUR"
  ],
  [
    23.4248006876139,
    20241002,
    "Azure Front Door Service",
    "EUR"
  ],
  [
    4.86812372899352,
    20241002,
    "Backup",
    "EUR"
  ],
  [
    0.0082495189270704,
    20241002,
    "Bandwidth",
    "EUR"
  ],
  [
    1.19393781901222,
    20241002,
    "Container Registry",
    "EUR"
  ],
  [
    0,
    20241002,
    "Insight and Analytics",
    "EUR"
  ],
  [
    1.611990853039,
    20241002,
    "Load Balancer",
    "EUR"
  ],
  [
    0,
    20241002,
    "Log Analytics",
    "EUR"
  ],
  [
    0,
    20241002,
    "Microsoft Defender for Cloud",
    "EUR"
  ],
  [
    3.01820150195504,
    20241002,
    "NAT Gateway",
    "EUR"
  ],
  [
    3.71826445170823,
    20241002,
    "Redis Cache",
    "EUR"
  ],
  [
    6.34559069977164,
    20241002,
    "Storage",
    "EUR"
  ],
  [
    14.9934205174406,
    20241002,
    "Virtual Machines",
    "EUR"
  ],
  [
    5.18118969008666,
    20241002,
    "Virtual Network",
    "EUR"
  ]
]

UQL :

parse-json
| jsonata "$map($, function($v, $i, $a) {{'value': $v[0],'date': $substring($string($v[1]),0,4) & "-" & $substring($string($v[1]),4,2) & "-" & $substring($string($v[1]),6,2),'metric': $v[2],'currency': $v[3]}})"
| project "date", "value", "metric"

And i have the error : No data in response

I just tried like you

UQL

parse-json
| jsonata "$map($, function($v, $i, $a) {{'value': $v[0],'date': $substring($string($v[1]),0,4) & "-" & $substring($string($v[1]),4,2) & "-" & $substring($string($v[1]),6,2),'metric': $v[2],'currency': $v[3]}})"
| project "date", "value", "metric"

Data

[
  [
    9.25597434792304,
    20241001,
    "Application Gateway",
    "EUR"
  ],
  [
    0,
    20241001,
    "Automation",
    "EUR"
  ],
  [
    16.2412401575407,
    20241001,
    "Azure App Service",
    "EUR"
  ],
  [
    6.23292884968432,
    20241001,
    "Azure Bastion",
    "EUR"
  ],
  [
    0.430322005180519,
    20241001,
    "Azure DNS",
    "EUR"
  ],
  [
    0.00000895535754264989,
    20241001,
    "Azure Data Factory v2",
    "EUR"
  ],
  [
    5.95997228413135,
    20241001,
    "Azure Database for MySQL",
    "EUR"
  ],
  [
    0.983587140684334,
    20241001,
    "Azure Database for PostgreSQL",
    "EUR"
  ],
  [
    10.601987800492,
    20241001,
    "Azure DevOps",
    "EUR"
  ],
  [
    24.5640844304826,
    20241001,
    "Azure Front Door Service",
    "EUR"
  ],
  [
    4.87307059365934,
    20241001,
    "Backup",
    "EUR"
  ],
  [
    0.00722409149740619,
    20241001,
    "Bandwidth",
    "EUR"
  ],
  [
    1.19393781901222,
    20241001,
    "Container Registry",
    "EUR"
  ],
  [
    0,
    20241001,
    "Insight and Analytics",
    "EUR"
  ],
  [
    1.61199901664664,
    20241001,
    "Load Balancer",
    "EUR"
  ],
  [
    0,
    20241001,
    "Log Analytics",
    "EUR"
  ],
  [
    0,
    20241001,
    "Microsoft Defender for Cloud",
    "EUR"
  ],
  [
    2.88339078296502,
    20241001,
    "NAT Gateway",
    "EUR"
  ],
  [
    3.71826445170823,
    20241001,
    "Redis Cache",
    "EUR"
  ],
  [
    6.30446578713115,
    20241001,
    "Storage",
    "EUR"
  ],
  [
    14.9138982492276,
    20241001,
    "Virtual Machines",
    "EUR"
  ],
  [
    5.0938043712376,
    20241001,
    "Virtual Network",
    "EUR"
  ],
  [
    11.2886013719307,
    20241002,
    "Application Gateway",
    "EUR"
  ],
  [
    0,
    20241002,
    "Automation",
    "EUR"
  ],
  [
    16.2412401575407,
    20241002,
    "Azure App Service",
    "EUR"
  ],
  [
    6.23292884968432,
    20241002,
    "Azure Bastion",
    "EUR"
  ],
  [
    0.384953682024142,
    20241002,
    "Azure DNS",
    "EUR"
  ],
  [
    5.52003252094759,
    20241002,
    "Azure Database for MySQL",
    "EUR"
  ],
  [
    0.983587140684334,
    20241002,
    "Azure Database for PostgreSQL",
    "EUR"
  ],
  [
    10.601987800492,
    20241002,
    "Azure DevOps",
    "EUR"
  ],
  [
    23.4248006876139,
    20241002,
    "Azure Front Door Service",
    "EUR"
  ],
  [
    4.86812372899352,
    20241002,
    "Backup",
    "EUR"
  ],
  [
    0.0082495189270704,
    20241002,
    "Bandwidth",
    "EUR"
  ],
  [
    1.19393781901222,
    20241002,
    "Container Registry",
    "EUR"
  ],
  [
    0,
    20241002,
    "Insight and Analytics",
    "EUR"
  ],
  [
    1.611990853039,
    20241002,
    "Load Balancer",
    "EUR"
  ],
  [
    0,
    20241002,
    "Log Analytics",
    "EUR"
  ],
  [
    0,
    20241002,
    "Microsoft Defender for Cloud",
    "EUR"
  ],
  [
    3.01820150195504,
    20241002,
    "NAT Gateway",
    "EUR"
  ],
  [
    3.71826445170823,
    20241002,
    "Redis Cache",
    "EUR"
  ],
  [
    6.34559069977164,
    20241002,
    "Storage",
    "EUR"
  ],
  [
    14.9934205174406,
    20241002,
    "Virtual Machines",
    "EUR"
  ],
  [
    5.18118969008666,
    20241002,
    "Virtual Network",
    "EUR"
  ]
]

But i have the error No data in response

What is your data looks like ?
I have an error :
No data in response

Data :

[
  [
    9.25597434792304,
    20241001,
    "Application Gateway",
    "EUR"
  ],
  [
    0,
    20241001,
    "Automation",
    "EUR"
  ],
  [
    16.2412401575407,
    20241001,
    "Azure App Service",
    "EUR"
  ],
  [
    6.23292884968432,
    20241001,
    "Azure Bastion",
    "EUR"
  ],
  [
    0.430322005180519,
    20241001,
    "Azure DNS",
    "EUR"
  ],
  [
    0.00000895535754264989,
    20241001,
    "Azure Data Factory v2",
    "EUR"
  ],
  [
    5.95997228413135,
    20241001,
    "Azure Database for MySQL",
    "EUR"
  ],
  [
    0.983587140684334,
    20241001,
    "Azure Database for PostgreSQL",
    "EUR"
  ],
  [
    10.601987800492,
    20241001,
    "Azure DevOps",
    "EUR"
  ],
  [
    24.5640844304826,
    20241001,
    "Azure Front Door Service",
    "EUR"
  ],
  [
    4.87307059365934,
    20241001,
    "Backup",
    "EUR"
  ],
  [
    0.00722409149740619,
    20241001,
    "Bandwidth",
    "EUR"
  ],
  [
    1.19393781901222,
    20241001,
    "Container Registry",
    "EUR"
  ],
  [
    0,
    20241001,
    "Insight and Analytics",
    "EUR"
  ],
  [
    1.61199901664664,
    20241001,
    "Load Balancer",
    "EUR"
  ],
  [
    0,
    20241001,
    "Log Analytics",
    "EUR"
  ],
  [
    0,
    20241001,
    "Microsoft Defender for Cloud",
    "EUR"
  ],
  [
    2.88339078296502,
    20241001,
    "NAT Gateway",
    "EUR"
  ],
  [
    3.71826445170823,
    20241001,
    "Redis Cache",
    "EUR"
  ],
  [
    6.30446578713115,
    20241001,
    "Storage",
    "EUR"
  ],
  [
    14.9138982492276,
    20241001,
    "Virtual Machines",
    "EUR"
  ],
  [
    5.0938043712376,
    20241001,
    "Virtual Network",
    "EUR"
  ],
  [
    11.2886013719307,
    20241002,
    "Application Gateway",
    "EUR"
  ],
  [
    0,
    20241002,
    "Automation",
    "EUR"
  ],
  [
    16.2412401575407,
    20241002,
    "Azure App Service",
    "EUR"
  ],
  [
    6.23292884968432,
    20241002,
    "Azure Bastion",
    "EUR"
  ],
  [
    0.384953682024142,
    20241002,
    "Azure DNS",
    "EUR"
  ],
  [
    5.52003252094759,
    20241002,
    "Azure Database for MySQL",
    "EUR"
  ],
  [
    0.983587140684334,
    20241002,
    "Azure Database for PostgreSQL",
    "EUR"
  ],
  [
    10.601987800492,
    20241002,
    "Azure DevOps",
    "EUR"
  ],
  [
    23.4248006876139,
    20241002,
    "Azure Front Door Service",
    "EUR"
  ],
  [
    4.86812372899352,
    20241002,
    "Backup",
    "EUR"
  ],
  [
    0.0082495189270704,
    20241002,
    "Bandwidth",
    "EUR"
  ],
  [
    1.19393781901222,
    20241002,
    "Container Registry",
    "EUR"
  ],
  [
    0,
    20241002,
    "Insight and Analytics",
    "EUR"
  ],
  [
    1.611990853039,
    20241002,
    "Load Balancer",
    "EUR"
  ],
  [
    0,
    20241002,
    "Log Analytics",
    "EUR"
  ],
  [
    0,
    20241002,
    "Microsoft Defender for Cloud",
    "EUR"
  ],
  [
    3.01820150195504,
    20241002,
    "NAT Gateway",
    "EUR"
  ],
  [
    3.71826445170823,
    20241002,
    "Redis Cache",
    "EUR"
  ],
  [
    6.34559069977164,
    20241002,
    "Storage",
    "EUR"
  ],
  [
    14.9934205174406,
    20241002,
    "Virtual Machines",
    "EUR"
  ],
  [
    5.18118969008666,
    20241002,
    "Virtual Network",
    "EUR"
  ]
]

 

UQL :

parse-json
| jsonata "$map($, function($v, $i, $a) {{'value': $v[0],'date': $substring($string($v[1]),0,4) & "-" & $substring($string($v[1]),4,2) & "-" & $substring($string($v[1]),6,2),'metric': $v[2],'currency': $v[3]}})"
| project "date", "value", "metric"

and i tried all formats

And you seem to have a Transform data ? :smile:


i just found this error !
This feature is not available for this type of query yet when not using format “as is”
What format do you use ?

You raw data keeps changing with every post

rows

properties.rows

what does the json data look like from the api or endpoint. and please dont post dashboard json :laughing:

From the query inspector it look like this !

Here i use the properties.rows,
With JSON and Backend Parser

{
  "request": {
    "url": "api/ds/query?ds_type=yesoreyeram-infinity-datasource&requestId=Q113",
    "method": "POST",
    "data": {
      "queries": [
        {
          "datasource": {
            "type": "yesoreyeram-infinity-datasource",
            "uid": "ee0fp0b2eyqdcb"
          },
          "columns": [],
          "filters": [],
          "format": "table",
          "global_query_id": "",
          "parser": "backend",
          "refId": "A",
          "root_selector": "properties.rows[*]",
          "source": "url",
          "type": "json",
          "url": "https://management.azure.com/subscriptions/xxxxx/providers/Microsoft.CostManagement/query?api-version=2023-11-01",
          "url_options": {
            "body_content_type": "application/json",
            "body_type": "raw",
            "data": "{\n  \"type\": \"Usage\",\n  \"timeframe\": \"Custom\",\n  \"timePeriod\": {\n    \"from\": \"2024-10-01\",\n    \"to\": \"2024-10-02\"\n  },\n  \"dataset\": {\n    \"granularity\": \"Daily\",\n    \"aggregation\": {\n      \"totalCost\": {\n        \"name\": \"Cost\",\n        \"function\": \"Sum\"\n      }\n    },\n    \"grouping\": [\n      {\n        \"type\": \"Dimension\",\n        \"name\": \"ServiceName\"\n      }\n    ]\n  }\n}",
            "method": "POST",
            "body_graphql_query": "",
            "body_graphql_variables": ""
          },
          "computed_columns": [],
          "filterExpression": "",
          "summarizeExpression": "",
          "datasourceId": 3,
          "intervalMs": 20000,
          "maxDataPoints": 1109
        }
      ],
      "from": "1728577546744",
      "to": "1728599146745"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "status": 200,
        "frames": [
          {
            "schema": {
              "name": "A",
              "refId": "A",
              "meta": {
                "typeVersion": [
                  0,
                  0
                ],
                "custom": {
                  "data": {
                    "eTag": null,
                    "id": "subscriptions/xxxx/providers/Microsoft.CostManagement/query/4xxx",
                    "location": null,
                    "name": "4c75b31c-6889-43b1-8325-0593c2fe8484",
                    "properties": {
                      "columns": [
                        {
                          "name": "Cost",
                          "type": "Number"
                        },
                        {
                          "name": "UsageDate",
                          "type": "Number"
                        },
                        {
                          "name": "ServiceName",
                          "type": "String"
                        },
                        {
                          "name": "Currency",
                          "type": "String"
                        }
                      ],
                      "nextLink": null,
                      "rows": [
                        [
                          9.25597434792304,
                          20241001,
                          "Application Gateway",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Automation",
                          "EUR"
                        ],
                        [
                          16.2412401575407,
                          20241001,
                          "Azure App Service",
                          "EUR"
                        ],
                        [
                          6.23292884968432,
                          20241001,
                          "Azure Bastion",
                          "EUR"
                        ],
                        [
                          0.430322005180519,
                          20241001,
                          "Azure DNS",
                          "EUR"
                        ],
                        [
                          0.00000895535754264989,
                          20241001,
                          "Azure Data Factory v2",
                          "EUR"
                        ],
                        [
                          5.95997228413135,
                          20241001,
                          "Azure Database for MySQL",
                          "EUR"
                        ],
                        [
                          0.983587140684334,
                          20241001,
                          "Azure Database for PostgreSQL",
                          "EUR"
                        ],
                        [
                          10.601987800492,
                          20241001,
                          "Azure DevOps",
                          "EUR"
                        ],
                        [
                          24.5640844304826,
                          20241001,
                          "Azure Front Door Service",
                          "EUR"
                        ],
                        [
                          4.87307059365934,
                          20241001,
                          "Backup",
                          "EUR"
                        ],
                        [
                          0.00722409149740619,
                          20241001,
                          "Bandwidth",
                          "EUR"
                        ],
                        [
                          1.19393781901222,
                          20241001,
                          "Container Registry",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Insight and Analytics",
                          "EUR"
                        ],
                        [
                          1.61199901664664,
                          20241001,
                          "Load Balancer",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Log Analytics",
                          "EUR"
                        ],
                        [
                          0,
                          20241001,
                          "Microsoft Defender for Cloud",
                          "EUR"
                        ],
                        [
                          2.88339078296502,
                          20241001,
                          "NAT Gateway",
                          "EUR"
                        ],
                        [
                          3.71826445170823,
                          20241001,
                          "Redis Cache",
                          "EUR"
                        ],
                        [
                          6.30446578713115,
                          20241001,
                          "Storage",
                          "EUR"
                        ],
                        [
                          14.9138982492276,
                          20241001,
                          "Virtual Machines",
                          "EUR"
                        ],
                        [
                          5.0938043712376,
                          20241001,
                          "Virtual Network",
                          "EUR"
                        ],
                        [
                          11.2886013719307,
                          20241002,
                          "Application Gateway",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Automation",
                          "EUR"
                        ],
                        [
                          16.2412401575407,
                          20241002,
                          "Azure App Service",
                          "EUR"
                        ],
                        [
                          6.23292884968432,
                          20241002,
                          "Azure Bastion",
                          "EUR"
                        ],
                        [
                          0.384953682024142,
                          20241002,
                          "Azure DNS",
                          "EUR"
                        ],
                        [
                          5.52003252094759,
                          20241002,
                          "Azure Database for MySQL",
                          "EUR"
                        ],
                        [
                          0.983587140684334,
                          20241002,
                          "Azure Database for PostgreSQL",
                          "EUR"
                        ],
                        [
                          10.601987800492,
                          20241002,
                          "Azure DevOps",
                          "EUR"
                        ],
                        [
                          23.4248006876139,
                          20241002,
                          "Azure Front Door Service",
                          "EUR"
                        ],
                        [
                          4.86812372899352,
                          20241002,
                          "Backup",
                          "EUR"
                        ],
                        [
                          0.0082495189270704,
                          20241002,
                          "Bandwidth",
                          "EUR"
                        ],
                        [
                          1.19393781901222,
                          20241002,
                          "Container Registry",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Insight and Analytics",
                          "EUR"
                        ],
                        [
                          1.611990853039,
                          20241002,
                          "Load Balancer",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Log Analytics",
                          "EUR"
                        ],
                        [
                          0,
                          20241002,
                          "Microsoft Defender for Cloud",
                          "EUR"
                        ],
                        [
                          3.01820150195504,
                          20241002,
                          "NAT Gateway",
                          "EUR"
                        ],
                        [
                          3.71826445170823,
                          20241002,
                          "Redis Cache",
                          "EUR"
                        ],
                        [
                          6.34559069977164,
                          20241002,
                          "Storage",
                          "EUR"
                        ],
                        [
                          14.9934205174406,
                          20241002,
                          "Virtual Machines",
                          "EUR"
                        ],
                        [
                          5.18118969008666,
                          20241002,
                          "Virtual Network",
                          "EUR"
                        ]
                      ]
                    },
                    "sku": null,
                    "type": "Microsoft.CostManagement/query"
                  },
                  "duration": 4268734301,
                  "error": "",
                  "query": {
                    "alias": "",
                    "columns": [],
                    "computed_columns": [],
                    "csv_options": {
                      "columns": "",
                      "comment": "",
                      "delimiter": "",
                      "relax_column_count": false,
                      "skip_empty_lines": false,
                      "skip_lines_with_error": false
                    },
                    "data": "",
                    "dataOverrides": null,
                    "expression": "",
                    "filterExpression": "",
                    "filters": [],
                    "format": "table",
                    "global_query_id": "",
                    "groq": "",
                    "json_options": {
                      "columnar": false,
                      "root_is_not_array": false
                    },
                    "parser": "backend",
                    "query_mode": "",
                    "refId": "A",
                    "root_selector": "properties.rows[*]",
                    "seriesCount": 0,
                    "source": "url",
                    "summarizeAlias": "",
                    "summarizeBy": "",
                    "summarizeExpression": "",
                    "type": "json",
                    "uql": "",
                    "url": "https://management.azure.com/subscriptions/xxxx/providers/Microsoft.CostManagement/query?api-version=2023-11-01",
                    "url_options": {
                      "body_content_type": "application/json",
                      "body_form": null,
                      "body_graphql_query": "",
                      "body_graphql_variables": "",
                      "body_type": "raw",
                      "data": "{\n  \"type\": \"Usage\",\n  \"timeframe\": \"Custom\",\n  \"timePeriod\": {\n    \"from\": \"2024-10-01\",\n    \"to\": \"2024-10-02\"\n  },\n  \"dataset\": {\n    \"granularity\": \"Daily\",\n    \"aggregation\": {\n      \"totalCost\": {\n        \"name\": \"Cost\",\n        \"function\": \"Sum\"\n      }\n    },\n    \"grouping\": [\n      {\n        \"type\": \"Dimension\",\n        \"name\": \"ServiceName\"\n      }\n    ]\n  }\n}",
                      "headers": null,
                      "method": "POST",
                      "params": null
                    }
                  },
                  "responseCodeFromServer": 200
                },
                "executedQueryString": "###############\n## URL\n###############\n\nhttps://management.azure.com/subscriptions/e8134c01-8d85-4322-b67b-0d4259dd8fdc/providers/Microsoft.CostManagement/query?api-version=2023-11-01\n\n###############\n## Curl Command\n###############\n\ncurl -X 'POST' -d '{\n  \"type\": \"Usage\",\n  \"timeframe\": \"Custom\",\n  \"timePeriod\": {\n    \"from\": \"2024-10-01\",\n    \"to\": \"2024-10-02\"\n  },\n  \"dataset\": {\n    \"granularity\": \"Daily\",\n    \"aggregation\": {\n      \"totalCost\": {\n        \"name\": \"Cost\",\n        \"function\": \"Sum\"\n      }\n    },\n    \"grouping\": [\n      {\n        \"type\": \"Dimension\",\n        \"name\": \"ServiceName\"\n      }\n    ]\n  }\n}' -H 'Accept: application/json;q=0.9,text/plain' -H 'Content-Type: application/json' 'https://management.azure.com/subscriptions/xx/providers/Microsoft.CostManagement/query?api-version=2023-11-01'\n###############\n> Authentication steps not included for OAuth authentication"
              },
              "fields": [
                {
                  "name": "A",
                  "type": "string",
                  "typeInfo": {
                    "frame": "string",
                    "nullable": true
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  "[9.25597434792304,20241001,\"Application Gateway\",\"EUR\"]",
                  "[0,20241001,\"Automation\",\"EUR\"]",
                  "[16.2412401575407,20241001,\"Azure App Service\",\"EUR\"]",
                  "[6.23292884968432,20241001,\"Azure Bastion\",\"EUR\"]",
                  "[0.430322005180519,20241001,\"Azure DNS\",\"EUR\"]",
                  "[0.00000895535754264989,20241001,\"Azure Data Factory v2\",\"EUR\"]",
                  "[5.95997228413135,20241001,\"Azure Database for MySQL\",\"EUR\"]",
                  "[0.983587140684334,20241001,\"Azure Database for PostgreSQL\",\"EUR\"]",
                  "[10.601987800492,20241001,\"Azure DevOps\",\"EUR\"]",
                  "[24.5640844304826,20241001,\"Azure Front Door Service\",\"EUR\"]",
                  "[4.87307059365934,20241001,\"Backup\",\"EUR\"]",
                  "[0.00722409149740619,20241001,\"Bandwidth\",\"EUR\"]",
                  "[1.19393781901222,20241001,\"Container Registry\",\"EUR\"]",
                  "[0,20241001,\"Insight and Analytics\",\"EUR\"]",
                  "[1.61199901664664,20241001,\"Load Balancer\",\"EUR\"]",
                  "[0,20241001,\"Log Analytics\",\"EUR\"]",
                  "[0,20241001,\"Microsoft Defender for Cloud\",\"EUR\"]",
                  "[2.88339078296502,20241001,\"NAT Gateway\",\"EUR\"]",
                  "[3.71826445170823,20241001,\"Redis Cache\",\"EUR\"]",
                  "[6.30446578713115,20241001,\"Storage\",\"EUR\"]",
                  "[14.9138982492276,20241001,\"Virtual Machines\",\"EUR\"]",
                  "[5.0938043712376,20241001,\"Virtual Network\",\"EUR\"]",
                  "[11.2886013719307,20241002,\"Application Gateway\",\"EUR\"]",
                  "[0,20241002,\"Automation\",\"EUR\"]",
                  "[16.2412401575407,20241002,\"Azure App Service\",\"EUR\"]",
                  "[6.23292884968432,20241002,\"Azure Bastion\",\"EUR\"]",
                  "[0.384953682024142,20241002,\"Azure DNS\",\"EUR\"]",
                  "[5.52003252094759,20241002,\"Azure Database for MySQL\",\"EUR\"]",
                  "[0.983587140684334,20241002,\"Azure Database for PostgreSQL\",\"EUR\"]",
                  "[10.601987800492,20241002,\"Azure DevOps\",\"EUR\"]",
                  "[23.4248006876139,20241002,\"Azure Front Door Service\",\"EUR\"]",
                  "[4.86812372899352,20241002,\"Backup\",\"EUR\"]",
                  "[0.0082495189270704,20241002,\"Bandwidth\",\"EUR\"]",
                  "[1.19393781901222,20241002,\"Container Registry\",\"EUR\"]",
                  "[0,20241002,\"Insight and Analytics\",\"EUR\"]",
                  "[1.611990853039,20241002,\"Load Balancer\",\"EUR\"]",
                  "[0,20241002,\"Log Analytics\",\"EUR\"]",
                  "[0,20241002,\"Microsoft Defender for Cloud\",\"EUR\"]",
                  "[3.01820150195504,20241002,\"NAT Gateway\",\"EUR\"]",
                  "[3.71826445170823,20241002,\"Redis Cache\",\"EUR\"]",
                  "[6.34559069977164,20241002,\"Storage\",\"EUR\"]",
                  "[14.9934205174406,20241002,\"Virtual Machines\",\"EUR\"]",
                  "[5.18118969008666,20241002,\"Virtual Network\",\"EUR\"]"
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

Not from query inspector, from the api

Maybe using another tool like insomnia or postman

Oh okey i didnt understand :

{
  "id": "subscriptions/xxx/providers/Microsoft.CostManagement/query/xx",
  "name": "xx",
  "type": "Microsoft.CostManagement/query",
  "location": null,
  "sku": null,
  "eTag": null,
  "properties": {
    "nextLink": null,
    "columns": [
      {
        "name": "Cost",
        "type": "Number"
      },
      {
        "name": "UsageDate",
        "type": "Number"
      },
      {
        "name": "ServiceName",
        "type": "String"
      },
      {
        "name": "Currency",
        "type": "String"
      }
    ],
    "rows": [
      [
        9.25597434792304,
        20241001,
        "Application Gateway",
        "EUR"
      ],
      [
        0,
        20241001,
        "Automation",
        "EUR"
      ],
      [
        6.23292884968432,
        20241001,
        "Azure Bastion",
        "EUR"
      ],
      [
        11.2886013719307,
        20241002,
        "Application Gateway",
        "EUR"
      ],
      [
        0,
        20241002,
        "Automation",
        "EUR"
      ],
      [
        6.23292884968432,
        20241002,
        "Azure Bastion",
        "EUR"
      ]
    ]
  }
}

this could be better !

now take that data blob and figure out the jsonata using https://try.jsonata.org/

So i foud this :

$map(properties.rows, function($v) {
  {
    'value': $v[0],
    'date': $substring($string($v[1]), 0, 4) & "-" & $substring($string($v[1]), 4, 2) & "-" & $substring($string($v[1]), 6, 2),
    'metric': $v[2],
    'currency': $v[3]
  }
})

that is giving me :

[
  {
    "value": 9.255974347923,
    "date": "2024-10-01",
    "metric": "Application Gateway",
    "currency": "EUR"
  },
  {
    "value": 0,
    "date": "2024-10-01",
    "metric": "Automation",
    "currency": "EUR"
  },
  {
    "value": 6.232928849684,
    "date": "2024-10-01",
    "metric": "Azure Bastion",
    "currency": "EUR"
  },
  {
    "value": 11.28860137193,
    "date": "2024-10-02",
    "metric": "Application Gateway",
    "currency": "EUR"
  },
  {
    "value": 0,
    "date": "2024-10-02",
    "metric": "Automation",
    "currency": "EUR"
  },
  {
    "value": 6.232928849684,
    "date": "2024-10-02",
    "metric": "Azure Bastion",
    "currency": "EUR"
  }
]

Now : i prepare my querry :
Type JSON, Parser UQL, Source URL, Data Frame : (i tried all)

and my UQL :

parse-json
| jsonata "$map(properties.rows, function($v) {{'value': $v[0],'date': $substring($string($v[1]), 0, 4) & "-" & $substring($string($v[1]), 4, 2) & "-" & $substring($string($v[1]), 6, 2), 'metric': $v[2],'currency': $v[3]}})"
| project "date", "value", "metric"

But still no result :confused:

Edit : i have an error : “error applying uql query”

If i use only the parse-json i have my json so my problem is after

maybe & "-" & to & '-' &