Which JSON Format Best For Power BI

[MrSujay]

Hi Everyone.

I am finding the best JSON format which can easily load into PowerBI.

Eg. This my current JSON Strucutre and and  iwant all data of element survey,relationships,demographics,Worker and thee childs.

How i can import this into PowerBI.

Or is any simple strucutre that support powerBI esaily. I have control on JSON.

{
  "surveys": {
    "2010": {
      "responseMetadata": {
        "surveyResponseDT": "2010-01-06T21:18:13.4409095-05:00",
        "surveyVersion": "2010"
      },
      "relationships": {
        "partner_organizations": [
          "MF Enterprise (MF STAFF ONLY)"
        ]
      },
      "Worker": {
        "job": [
          {
            "version": "2010",
            "stringValue": "employee care worker"
          }
        ]
      },
      "demographics": {
        "discribe_work_settings": [
          {
            "version": "2010",
            "stringValue": "Employed in a field other than employee"
          }
        ]
      }
    }
  },
  "relationships": {
    "partner_organizations": {
      "partnerSurveys": {
        "MF Enterprise (MF STAFF ONLY)": [
          "2010"
        ]
      }
    }
  },
  "demographics": {
    "discribe_work_settings": {
      "versionValues": [
        {
          "version": "2010",
          "stringValue": "Employed in a field other than nursing"
        }
      ]
    }
  },
  "Worker": {
    "job": {
      "versionValues": [
        {
          "version": "2010",
          "stringValue": "employee care worker"
        }
      ]
    }
  }
}

[amitchandak · Accepted solution]

@MrSujay , I checked recently power bi easily import an array of Hashmap (And again it has an array of the hash map)

In fact, I do not have to anything about that. But it should work with all standard formats. You might have to take some manual steps

example

[
{
  "orderId": 1,
  "orderNumber": "TEST1",
  "orderDate": "2020-07-01",
  "createDate": "2020-07-01",
  "modifyDate": "2020-07-01",
  "shipDate": "2015-07-05",
  "orderStatus": "Ordered",
  "customerId": 123,
  "items": [
    {
      "orderlineid": 1,
      "itemid": "1",
      "itemkey": "item1",
      "name": "Item #1",
      "quantity": 3,
      "unitPrice": 2,
      "taxAmount": 0,
      "createDate": "2015-07-01",
      "modifyDate": "2015-07-01"
    },
	{
      "orderlineid": 2,
      "itemid": "2",
      "itemkey": "item2",
      "name": "Item #2",
      "quantity": 3,
      "unitPrice": 4,
      "taxAmount": 0,
      "createDate": "2020-07-01",
      "modifyDate": "2020-07-01"
    }
  ],
  "orderTotal": 18,
  "discountAmout": 0
},
{
  "orderId": 2,
  "orderNumber": "TEST2",
  "orderDate": "2020-07-01",
  "createDate": "2020-07-01",
  "modifyDate": "2020-07-01",
  "shipDate": "2015-07-05",
  "orderStatus": "Ordered",
  "customerId": 234,
  "items": [
    {
      "orderlineid": 1,
      "itemid": "3",
      "itemkey": "item3",
      "name": "Item #3",
      "quantity": 5,
      "unitPrice": 6,
      "taxAmount": 0,
      "createDate": "2015-07-01",
      "modifyDate": "2015-07-01"
    },
	{
      "orderlineid": 2,
      "itemid": "2",
      "itemkey": "item2",
      "name": "Item #2",
      "quantity": 6,
      "unitPrice": 4,
      "taxAmount": 0,
      "createDate": "2020-07-01",
      "modifyDate": "2020-07-01"
    }
  ],
  "orderTotal": 54,
  "discountAmout": 0
}
]

also refer these for how work with JSON

https://www.youtube.com/watch?v=ipI6mrWLQKA
https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0...