Forum Discussion
Query CCG JSON url to ShapeMap in PowerBI
Hello, really hope you can help.
I'm stuck trying to get the shapes of CCGs...
... into a PowerBI dashboard. (I can do this with KML files in Excel no problem, but this is beating me...)
All help much appreciated ...
Hi,
here is a Power Query for you who hopefully will solve your problem.
let Source = Json.Document(Web.Contents("https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/CCG_APR_2019_EN_NC/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json")), features = Source[features], #"convertedtoTable" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"expandedColumn" = Table.ExpandRecordColumn(#"convertedtoTable", "Column1", {"attributes"}, {"Column1.attributes"}), #"expandedAttributes" = Table.ExpandRecordColumn(#"expandedColumn", "Column1.attributes", {"CCG19CD", "CCG19CDH", "CCG19NM", "FID"}, {"Column1.attributes.CCG19CD", "Column1.attributes.CCG19CDH", "Column1.attributes.CCG19NM", "Column1.attributes.FID"}) in #"expandedAttributes"I think you only need the arrtibutes ?
I leave the column names unformatted but you can rename as you like.
The Output will look like this.
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
4 Replies
Hi,
here is a Power Query for you who hopefully will solve your problem.
let Source = Json.Document(Web.Contents("https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/CCG_APR_2019_EN_NC/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json")), features = Source[features], #"convertedtoTable" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"expandedColumn" = Table.ExpandRecordColumn(#"convertedtoTable", "Column1", {"attributes"}, {"Column1.attributes"}), #"expandedAttributes" = Table.ExpandRecordColumn(#"expandedColumn", "Column1.attributes", {"CCG19CD", "CCG19CDH", "CCG19NM", "FID"}, {"Column1.attributes.CCG19CD", "Column1.attributes.CCG19CDH", "Column1.attributes.CCG19NM", "Column1.attributes.FID"}) in #"expandedAttributes"I think you only need the arrtibutes ?
I leave the column names unformatted but you can rename as you like.
The Output will look like this.
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!Thank you! Defintiey progress - most of the CCGs now show up in the right place 🙂
However, weirdly a number show up in the wrong place - typically in American places with similar names.
Do you know if that is a problem with the query, or with the data?
Hi TomTomTom ,
the Query do the same thing for every attribute.
in fact not every row is wrong I think it's a problem with the data, may be there is a missing comma...
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
