Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
VIC-O
Frequent Visitor

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.

 

 

 

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

Hi @VIC-O 

Check this out

https://www.sqlshack.com/importing-data-from-json-files-and-power-bi-rest-apis-into-power-bi/

https://www.youtube.com/watch?v=ipI6mrWLQKA

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

3 REPLIES 3
aj1973
Community Champion
Community Champion

Hi @VIC-O 

Check this out

https://www.sqlshack.com/importing-data-from-json-files-and-power-bi-rest-apis-into-power-bi/

https://www.youtube.com/watch?v=ipI6mrWLQKA

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

VIC-O
Frequent Visitor

I cant see that bubble to narrow it down in mine.

aj1973
Community Champion
Community Champion

What Bubble?

aj1973_0-1618869331924.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.