Created
December 27, 2019 13:34
-
-
Save FrieseWoudloper/68de3449287c9ed43ac4f140d3991e4c to your computer and use it in GitHub Desktop.
Power BI query om onvolledige en foute adressen van provinciehuizen te verbeteren en verrijken met LocatieServer
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY9ba4NAEIX/yuCzD3G9YB6VQKRNoFB6QcnD6E50m81YZldK/311m8c53+E7TNdFrx6ekJ3zgughi+EoMxseiaNL3EXlPkmeW8hVuD7IeZJeDN8giWGf7XII4LR4T9yTjKsIFLQ/s7UU0Bnl5iFJoBKe6B6yd+NQiPUmKVWqXho4kf11HnXglQyTuZMmZxEZihjSvMyg/oQ3LzRMPrQOQnSFFBpEsQ9zuxgNzTqOrL8tGd4mDsRbaQyNWrBH9kH8YPXshinAZAeFUns4Vv9/Vb02X1DA2WhNtl9kdVz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [adres = _t]), | |
#"Changed Type" = Table.TransformColumnTypes(Source,{{"adres", type text}}), | |
#"Build URL" = Table.AddColumn(#"Changed Type", "URL", each "https://geodata.nationaalgeoregister.nl/locatieserver/v3/free?fq=type:adres AND bron:BAG&fl=*&rows=1&q=" & [adres]), | |
#"Get Attributes" = Table.AddColumn(#"Build URL", "attrs", each Json.Document(Web.Contents([URL]))[response][docs]{0}), | |
#"Get wijkcode" = Table.AddColumn(#"Get Attributes", "wijkcode", each [attrs][wijkcode]), | |
#"Get buurtcode" = Table.AddColumn(#"Get wijkcode", "buurtcode", each [attrs][buurtcode]), | |
#"Get woonplaats" = Table.AddColumn(#"Get buurtcode", "woonplaatsnaam", each [attrs][woonplaatsnaam]), | |
#"Get postcode" = Table.AddColumn(#"Get woonplaats", "postcode", each [attrs][postcode]), | |
#"Get straat" = Table.AddColumn(#"Get postcode", "straatnaam", each [attrs][straatnaam]), | |
#"Get huisnummer" = Table.AddColumn(#"Get straat", "huis_nlt", each [attrs][huis_nlt]), | |
#"Get ID" = Table.AddColumn(#"Get huisnummer", "adresseerbaarobject_id", each [attrs][adresseerbaarobject_id]), | |
#"Get coordinates" = Table.AddColumn(#"Get ID", "centroide_ll", each [attrs][centroide_ll]), | |
#"Replaced Value" = Table.ReplaceValue(#"Get coordinates","POINT(","",Replacer.ReplaceText,{"centroide_ll"}), | |
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"centroide_ll"}), | |
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "centroide_ll", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"longitude", "latitude"}), | |
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"longitude", type number}, {"latitude", type number}}), | |
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"URL", "attrs", "adresseerbaarobject_id"}) | |
in | |
#"Removed Columns" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment