Forum Discussion

VIC-O's avatar
VIC-O
Frequent Visitor
5 years ago
Solved

How to convert json into a table

Hello All, 

I have seen other examples, but none seems to look mine here, I have the below JSON data, how do I convert it to a table:

 

 

{
    "meta": {
        "@id": "http://environment.data.gov.uk/hydrology/id/stations.json?_limit=2",
        "publisher": "Environment Agency",
        "license": "http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/",
        "licenseName": "OGL 3",
        "comment": "Test version of hydrology API, alpha",
        "version": "0.2.0",
        "hasFormat": [
            "http://environment.data.gov.uk/hydrology/id/stations.html?_limit=2",
            "http://environment.data.gov.uk/hydrology/id/stations.csv?_limit=2",
            "http://environment.data.gov.uk/hydrology/id/stations.json?_limit=2",
            "http://environment.data.gov.uk/hydrology/id/stations.ttl?_limit=2",
            "http://environment.data.gov.uk/hydrology/id/stations.geojson?_limit=2",
            "http://environment.data.gov.uk/hydrology/id/stations.rdf?_limit=2"
        ],
        "limit": 2
    },
    "items": [
        {
            "@id": "http://environment.data.gov.uk/hydrology/id/stations/951f7162-24d4-45f4-a03e-ee55d3c864c1",
            "type": [
                {
                    "@id": "http://environment.data.gov.uk/flood-monitoring/def/core/Station"
                },
                {
                    "@id": "http://environment.data.gov.uk/reference/def/core/SamplingLocation"
                }
            ],
            "label": "Ponsanooth",
            "notation": "951f7162-24d4-45f4-a03e-ee55d3c864c1",
            "easting": 176228,
            "northing": 37642,
            "lat": 50.196411,
            "long": -5.136703,
            "riverName": "River Kennall",
            "stationReference": "48183",
            "wiskiID": "SW73F053",
            "RLOIid": "3385",
            "nrfaStationID": "48007",
            "nrfaStationURL": "https://nrfa.ceh.ac.uk/data/station/info/48007.html",
            "stationGuid": "951f7162-24d4-45f4-a03e-ee55d3c864c1",
            "measures": [
                {
                    "@id": "http://environment.data.gov.uk/hydrology/id/measures/951f7162-24d4-45f4-a03e-ee55d3c864c1-flow-m-86400-m3s-qualified",
                    "label": "Qualified daily mean flow timeseries for SW73F053(Ponsanooth)",
                    "observedProperty": {
                        "@id": "http://environment.data.gov.uk/reference/def/op/waterFlow",
                        "label": "Water Flow"
                    },
                    "unitName": "m3/s",
                    "notation": "951f7162-24d4-45f4-a03e-ee55d3c864c1-flow-m-86400-m3s-qualified",
                    "period": 86400,
                    "valueStatistic": {
                        "@id": "http://environment.data.gov.uk/reference/def/core/mean",
                        "label": "mean"
                    },
                    "observationType": {
                        "@id": "http://environment.data.gov.uk/reference/def/core/Qualified",
                        "label": "Qualified"
                    }
                },
                {
                    "@id": "http://environment.data.gov.uk/hydrology/id/measures/951f7162-24d4-45f4-a03e-ee55d3c864c1-flow--i-15_min-m3_s-measured",
                    "label": "Measured flow 15 min timeseries for Ponsanooth",
                    "observedProperty": {
                        "@id": "http://environment.data.gov.uk/reference/def/op/waterFlow",
                        "label": "Water Flow"
                    },
                    "unitName": "m3/s",
                    "notation": "951f7162-24d4-45f4-a03e-ee55d3c864c1-flow--i-15_min-m3_s-measured",
                    "period": 900,
                    "valueStatistic": {
                        "@id": "http://environment.data.gov.uk/reference/def/core/mean",
                        "label": "mean"
                    },
                    "observationType": {
                        "@id": "http://environment.data.gov.uk/reference/def/core/Measured",
                        "label": "Measured"
                    }
                }
            ]
        },
        {
            "@id": "http://environment.data.gov.uk/hydrology/id/stations/d9f8ae6e-a12e-4fbf-972e-21c89e521789",
            "type": [
                {
                    "@id": "http://environment.data.gov.uk/flood-monitoring/def/core/Station"
                },
                {
                    "@id": "http://environment.data.gov.uk/reference/def/core/SamplingLocation"
                }
            ],
            "label": "Brent (Monks Park)",
            "notation": "d9f8ae6e-a12e-4fbf-972e-21c89e521789",
            "easting": 520194,
            "northing": 185052,
            "lat": 51.551558,
            "long": -0.267837,
            "riverName": "River Brent",
            "stationReference": "3850TH",
            "wiskiID": "3850TH",
            "nrfaStationID": "39093",
            "nrfaStationURL": "https://nrfa.ceh.ac.uk/data/station/info/39093.html",
            "stationGuid": "d9f8ae6e-a12e-4fbf-972e-21c89e521789",
            "measures": {
                "@id": "http://environment.data.gov.uk/hydrology/id/measures/d9f8ae6e-a12e-4fbf-972e-21c89e521789-flow-m-86400-m3s-qualified",
                "label": "Qualified daily mean flow timeseries for 3850TH(Brent (Monks Park))",
                "observedProperty": {
                    "@id": "http://environment.data.gov.uk/reference/def/op/waterFlow",
                    "label": "Water Flow"
                },
                "unitName": "m3/s",
                "notation": "d9f8ae6e-a12e-4fbf-972e-21c89e521789-flow-m-86400-m3s-qualified",
                "period": 86400,
                "valueStatistic": {
                    "@id": "http://environment.data.gov.uk/reference/def/core/mean",
                    "label": "mean"
                },
                "observationType": {
                    "@id": "http://environment.data.gov.uk/reference/def/core/Qualified",
                    "label": "Qualified"
                }
            }
        }
    ]
}

 

 

 

Currently, it comes out like this in Power BI:

meta : Record

items: List

 

Sorry, I just don't know how to attach pictures in the post.

 

 

Thanks.