0

So I've recently been tasked with finding a way to retrieve data from an api call, then upload that data back to a SharePoint list item and all followed by that it has to be pulled back into a PowerBi dashboard template. I've done this for a separate api call already but that reurned data was not nested and did not have variable amounts of objects in the data. This is an example of what the json data looks like. This response is only for one company and I'm already pulling and pushing multiple companies with separate data into the SharePoint List.

[
{
    "date": "2017-06-24",
    "guid": "ad9ee957-1077-421e-8318-ec4375ffd3a1",
    "start_score": 760,
    "end_score": 740,
    "reasons": [
        {
            "start_percentile": 77.79417,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 760,
            "end_percentile": 59.94863,
            "end_score": 710
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
},
{
    "date": "2017-07-01",
    "guid": "677f2129-46e2-4d47-93cc-51dd254a8414",
    "start_score": 760,
    "end_score": 740,
    "reasons": [
        {
            "start_percentile": 75.83092,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 750,
            "end_percentile": 59.6297,
            "end_score": 710
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
},
{
    "date": "2017-08-02",
    "guid": "927a9e11-0596-42ec-9eab-5fe2817d120e",
    "start_score": 770,
    "end_score": 750,
    "reasons": [
        {
            "start_percentile": 84.50854,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 780,
            "end_percentile": 67.62322,
            "end_score": 730
        },
        {
            "start_percentile": 61.19642,
            "risk_vector": "http_headers",
            "evidence": [
                {
                    "end_grade": "D",
                    "start_grade": "C"
                }
            ],
            "start_score": 720,
            "end_percentile": 36.23348,
            "end_score": 640
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
},
{
    "date": "2017-09-14",
    "guid": "c0e3a4b6-e6fa-4574-ab41-764245177744",
    "start_score": 760,
    "end_score": 740,
    "reasons": [
        {
            "start_percentile": 72.6813,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 750,
            "end_percentile": 58.43455,
            "end_score": 710
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
},
{
    "date": "2018-01-04",
    "guid": "f5ea7c00-d453-4d98-a501-4740d45100bd",
    "start_score": 760,
    "end_score": 740,
    "reasons": [
        {
            "start_percentile": 66.71679,
            "risk_vector": "certificate",
            "evidence": [
                {
                    "end_grade": "B",
                    "start_grade": "C"
                }
            ],
            "start_score": 730,
            "end_percentile": 71.17681,
            "end_score": 740
        },
        {
            "start_percentile": 79.28206,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 760,
            "end_percentile": 59.34212,
            "end_score": 710
        },
        {
            "start_percentile": 68.1736,
            "risk_vector": "http_headers",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "C"
                }
            ],
            "start_score": 730,
            "end_percentile": 57.86065,
            "end_score": 710
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
},
{
    "date": "2018-01-14",
    "guid": "135bfc86-c71a-400b-88ef-3448a3e96b0e",
    "start_score": 760,
    "end_score": 740,
    "reasons": [
        {
            "start_percentile": 73.14545,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 750,
            "end_percentile": 59.2746,
            "end_score": 710
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
},
{
    "date": "2018-03-20",
    "guid": "c8e02841-868e-478b-968a-cd03f9599fdc",
    "start_score": 770,
    "end_score": 750,
    "reasons": [
        {
            "start_percentile": 85.13657,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 780,
            "end_percentile": 69.31865,
            "end_score": 740
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
},
{
    "date": "2018-03-27",
    "guid": "4264374a-8203-4ef9-bf92-37281b58e047",
    "start_score": 770,
    "end_score": 750,
    "reasons": [
        {
            "start_percentile": 78.65493,
            "risk_vector": "open_port",
            "evidence": [
                {
                    "end_grade": "C",
                    "start_grade": "B"
                }
            ],
            "start_score": 760,
            "end_percentile": 69.69274,
            "end_score": 740
        }
    ],
    "type": "rating-change",
    "entity": "00255847-e950-4ed5-ac90-33b947613b19",
    "algorithm_update": false
}

]

My feeling is that I may need to dynamically create new columns in the the SharePoint list based on what is returned but otherwise I'm lost as to how I may be able to create a meaningful graph from this data.

  • Can you just hook PowerBI up to the api directly? – Graham Jun 12 '18 at 14:45
  • Unfortunately they are looking to limit how many times the API is called due to costs and so therefore have the results saved, and plus add their own input to the data. – Jherb Jun 12 '18 at 15:01
  • Plus it seems PowerBi doesn't play nice with nested json data either. – Jherb Jun 12 '18 at 15:02
  • What if you used two SharePoint lists, one for the base data, and one for the 1-many "reasons" data? You could connect the two using a lookup column on the "entity" field, and using connected lookup fields, you could pull in the rest of the base data into the "reasons" list. – Robin Jun 12 '18 at 18:52
  • Create a service that reads a stored version of the data to a database (either in full or with the JSON data just stored as a value in a single column) then point PowerBI to it. If you point an external data list at the JSON from the API (which you can do), you'd still run into an issue with the frequency with which the API is hit. – Graham Jun 12 '18 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.