Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to Solution.
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
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
I cant see that bubble to narrow it down in mine.
What Bubble?
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
117 | |
84 | |
49 | |
38 | |
28 |
User | Count |
---|---|
188 | |
76 | |
73 | |
54 | |
42 |