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.
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
Solved! Go to 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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |