Forum Discussion

jimmyswoosh's avatar
jimmyswoosh
Advocate I
7 years ago
Solved

Need Help With Parsing JSON/DataBag Format

Hi Power BI Community!
 
I'm in need of help on how to parse a column with string value in a JSON/DataBag structure. Normally I would use the auto splitter that's present at the column headers but since the data isn't a record or list, this option isn't available.
 
Here's the sample dataset of what I'm starting with
 
Starting Data
Here's what I'm trying to get to
 
Expected Results
 
JSON/DataBag Prettified
 
{
  "request": [
    {
      "id": "|+98jfjs-jf9",
      "name": "GET /api/lists/multi/HeroList",
      "count": 1000,
      "responseCode": 200,
      "success": true,
      "url": "https://example.com/api/lists/multi/HeroList?locale=en-us",
      "urlData": {
        "base": "/api/lists/multi/HeroList",
        "host": "example.com",
        "hashTag": "",
        "protocol": "https"
      },
      "durationMetric": {
        "value": 50922000,
        "count": 1000,
        "min": 50922,
        "max": 50922,
        "stdDev": 0,
        "sampledValue": 50922
      }
    }
  ],
  "internal": {
    "data": {
      "id": "546g456-gdfg335g-453reh45g",
      "documentVersion": "1.61"
    }
  },
  "context": {
    "data": {
      "eventTime": "2018-11-28T15:55:42.228548Z",
      "isSynthetic": false,
      "samplingRate": 0.1
    },
    "cloud": {},
    "device": {
      "type": "PC",
      "roleName": "admin",
      "roleInstance": "FSLDFJ3485345J",
      "screenResolution": {}
    },
    "user": {
      "anonId": "GHG890",
      "isAuthenticated": false
    },
    "session": {
      "id": "NJF98fj",
      "isFirst": false
    },
    "operation": {
      "id": "+78FJI",
      "parentId": "|+5myn.QNAGN",
      "name": "GET /api/lists/multi/HeroList"
    },
    "location": {
      "clientip": "0.0.0.0",
      "continent": "North America",
      "country": "United States"
    },
    "custom": {
      "dimensions": [
        {
          "_test.ProcessedByMetricExtractors": "(Name:'Requests', Ver:'1.0')"
        },
        {
          "client-ip": "00.00.000.00"
        }
      ]
    }
  }
}
 
Thanks,
-James
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, jimmyswoosh

    After my test, you may try this way as below:
    Step1:

    Select column 1 then click Add column->From text->Parse->Json

    Step2:

    Expand JSON.request

    201811291

     Step3:

    Expand other columns

    If there are list in the column, Expand to New Rows, then Expand column.

     

    Best Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, jimmyswoosh

    After my test, you may try this way as below:
    Step1:

    Select column 1 then click Add column->From text->Parse->Json

    Step2:

    Expand JSON.request

    201811291

     Step3:

    Expand other columns

    If there are list in the column, Expand to New Rows, then Expand column.

     

    Best Regards,

    Lin