Geocoding van adressen met LocatieServer en Power Query in Power BI

query.txt

let
    Source = Csv.Document(File.Contents("C:\Users\Willy\Downloads\export_opendata_lrk (1).csv"),[Delimiter=";", Columns=39, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"lrk_id", Int64.Type}, {"type_oko", type text}, {"actuele_naam_oko", type text}, {"aantal_kindplaatsen", Int64.Type}, {"vve", type text}, {"status", type text}, {"inschrijfdatum", type date}, {"opvanglocatie_adres", type text}, {"opvanglocatie_postcode", type text}, {"opvanglocatie_woonplaats", type text}, {"bag_id", Int64.Type}, {"naam_houder", type text}, {"kvk_nummer_houder", Int64.Type}, {"vestigingsnummer_houder", Int64.Type}, {"rechtsvorm_houder", type text}, {"houder_adres", type text}, {"houder_postcode", type text}, {"houder_woonplaats", type text}, {"houder_buitenland", type text}, {"correspondentie_adres", type text}, {"correspondentie_postcode", type text}, {"correspondentie_woonplaats", type text}, {"correspondentie_buitenland", type text}, {"contact_persoon", type text}, {"contact_telefoon", type text}, {"contact_emailadres", type text}, {"contact_website", type text}, {"cbs_code", Int64.Type}, {"verantwoordelijke_gemeente", type text}, {"gastouderbureau_1", Int64.Type}, {"gastouderbureau_2", Int64.Type}, {"gastouderbureau_3", Int64.Type}, {"gastouderbureau_4", Int64.Type}, {"gastouderbureau_5", Int64.Type}, {"gastouderbureau_6", Int64.Type}, {"gastouderbureau_7", Int64.Type}, {"gastouderbureau_8", type text}, {"gastouderbureau_9", type text}, {"gastouderbureau_10", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "adres", each [opvanglocatie_adres] & " " & [opvanglocatie_postcode] & " " & [opvanglocatie_woonplaats]),
    #"Build URL" = Table.AddColumn(#"Added Custom", "URL", each "https://geodata.nationaalgeoregister.nl/locatieserver/v3/free?fq=type:adres AND bron:BAG&fl=wijkcode, buurtcode, woonplaatsnaam, postcode, straatnaam, huis_nlt, centroide_ll, adresseerbaarobject_id&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"})
in
    #"Removed Columns"