Question 103222 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – How to Expand JSON data in powerBI?

I am trying to read a JSON file in powerBI whose data looks like this, But when I expend the column timeseries.data, I can only see ‘timestamp’ column instead of ‘timestamp’ and ‘average’

    "cost":  44639,
    "timespan":  "2023-02-10T23:59:59Z/2023-03-13T23:59:59Z",
    "interval":  "PT1H",
    "value":  [
                  {
                      "id":  "xxxx",
                      "type":  "Microsoft.Insights/metrics",
                      "name":  {
                                   "value":  "UsedCapacity",
                                   "localizedValue":  "Used capacity"
                               },
                      "displayDescription":  "The amount of storage used by the storage account. For standard storage accounts, itu0027s the sum of capacity used by blob, table, file, and queue. For premium storage accounts and Blob storage accounts, it is the same as BlobCapacity or FileCapacity.",
                      "unit":  "Bytes",
                      "timeseries":  [
                                         {
                                             "metadatavalues":  [

                                                                ],
                                             "data":  [
                                                          {
                                                              "timeStamp":  "2023-02-10T23:59:00Z"
                                                          },
                                                          {
                                                              "timeStamp":  "2023-02-11T00:59:00Z"
                                                          },
                                                          {
                                                              "timeStamp":  "2023-02-11T01:59:00Z"
                                                          },
                                                          {
                                                              "timeStamp":  "2023-02-11T02:59:00Z"
                                                          },
                                                          {
                                                              "timeStamp":  "2023-02-11T03:59:00Z"
                                                          },
                                                          {
                                                              "timeStamp":  "2023-02-11T04:59:00Z"
                                                          },
                                                          {  "timeStamp":  "2023-03-10T06:59:00Z","average": 19148425}  ],
                      "errorCode":  "Success"
                  }
              ],
    "namespace":  "Microsoft.Storage/storageAccounts",
    "resourceregion":  "eastus"

data in powerBI

Tags:

2

Answers


  1. 0

    It looks like your top 1000 records do not contain the average key-value pair. You can expand the column as-is and amend the auto-generated formula to expand average as well, for records where this exists.

    You can also try to re-order your table so that you have records in the top 1000 containing average, so you can expand without editing the automatic code generated on expand.

    Login or Signup to reply.
  2. 0

    This is because scanned 1000 rows do not have average column.
    enter image description here

    In order to expand timeseries.data into columns timestamp and average in the next step, you can change the query as below.

    = Table.ExpandRecordColumn(#"Expanded value.timeseries.data", "value.timeseries.data", {"timeStamp","average"}, 
{"value.timeseries.data.timeStamp","value.timeseries.data.average"})

    enter image description here

    • You can verify the data in average column by sorting the data based on the column timeseries.data.average.

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search