England Counties JSON

[Ttaylor9870]

Hi All,

I am looking for a JSON file containing all england counties does anyone know of a wesbite that would contain this?

Many Thanks,

Taylor

[tackytechtom · Accepted solution]

Hi @Ttaylor9870 ,

I did a bit of googling and I am surprised that, indeed, there might be nothing out there. Perhaps that's a chance for a small business 😄 ...

Anyway, not sure whether you like this suggestion, but maybe wikipedia can help as a workaround?
The result:

And here how I did it:

Click on Get Data, use the WEB connector and paste the following URL into the URL bar:

https://en.wikipedia.org/wiki/List_of_counties_of_the_United_Kingdom

Next, select table2:

Lastly, do some cleansing:

Here the full code in M that you should be able to copy as is:

let
    Source = Web.BrowserContents("https://en.wikipedia.org/wiki/List_of_counties_of_the_United_Kingdom"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7)"}, {"Column2", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(5), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6)"}, {"Column3", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(5)"}, {"Column4", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(5)"}, {"Column5", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH[rowspan=""2""]:not([colspan]):nth-child(4):nth-last-child(3), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4)"}, {"Column6", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH[rowspan=""2""]:not([colspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3)"}, {"Column7", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH[rowspan=""2""]:not([colspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(1), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(2), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(2)"}, {"Column8", "TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[rowspan=""2""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""2""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(4) + TH[rowspan=""2""]:not([colspan]):nth-child(4):nth-last-child(3) + TH[colspan=""2""]:not([rowspan]):nth-child(5):nth-last-child(2) + TH[rowspan=""2""]:not([colspan]):nth-child(6):nth-last-child(1), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(1), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(6) + TD[colspan=""2""]:not([rowspan]):nth-child(3):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(1)"}}, [RowSelector="TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"County", type text}, {"Currentceremonial[2]", type text}, {"From 1974[3]", type text}, {"From 1974[3]_1", type text}, {"Postal1974–1996[4]", type text}, {"1889–1974", type text}, {"1889–1974_2", type text}, {"Before 1889", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [County] <> null and [County] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Currentceremonial[2]", "From 1974[3]", "From 1974[3]_1", "Postal1974–1996[4]", "1889–1974", "1889–1974_2", "Before 1889"})
in
    #"Removed Columns"

You are of course right, you should be using i.e. an API service, instead of letting Power BI read the HTML of wikipedia. This is quite a vulnerable one and I wouldn't use it in an enterprise project. But for small ones, it might do the job. 

Since I suppose you are having that data already somewhere on your transactional fact table, I wonder whether your source system couldn't provide the dimension table as well?

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[masplin]

this might be counties https://github.com/deldersveld/topojson/blob/master/countries/united-kingdom/uk-counties.json 

[masplin]

This one works great for one level down LA LA JSON 

This guy did a load of useful ones, but dont think counties https://github.com/martinjc/UK-GeoJSON/tree/master