Query CCG JSON url to ShapeMap in PowerBI

[TomTomTom]

Hello, really hope you can help.

I'm stuck trying to get the shapes of CCGs...

https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/CCG_APR_2019_EN_NC/FeatureServer/...

... into a PowerBI dashboard. (I can do this with KML files in Excel no problem, but this is beating me...)

All help much appreciated ...

[rainer1 · Accepted solution]

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!

[TomTomTom]

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?

[rainer1]

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!

[TomTomTom]

Thanks. I am going to mark your script above as the solution and query with the people who created the data. Cheers.