Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ...
Solved! Go to 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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |