Forum Discussion
Query CCG JSON url to ShapeMap in PowerBI
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!
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!Thanks. I am going to mark your script above as the solution and query with the people who created the data. Cheers.