You are changing the types of the columns in the last step
= Table.TransformColumnTypes(#"Promoted Headers",{{"house_number", Int64.Type},
{"road", type text}, {"city", type text}, {"postcode", type text}, {"country", type text}})The problem is that not each coordinate has a house number. so the column does not exists in
this steps, and it fails.
So you need to make sure you always have a result for house_number.
You can achieve this by using "adding data", and give blank results for each option
I named the table DefaultValues
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs4sqVTSUVKK1YlWKsgvLknOT0mF8ZPzS/NKiuDSGfmlxanxeaW5SalFMLGi/MQUMDsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", type text}})
in
#"Changed Type"
Then you can change your function to add these records, and remove the duplicates:
let
AddressLookup = (lat as text, long as text) =>
let
Source = Json.Document(Web.Contents("http://nominatim.openstreetmap.org/reverse?format=jsonv2&lat="&lat&"&lon="&long&"")),
address = Source[address],
#"Converted to Table" = Record.ToTable(address),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "city" or [Name] = "country" or [Name] = "house_number" or [Name] = "postcode" or [Name] = "road")),
#"Appended Query" = Table.Combine({#"Filtered Rows", DefaultValues}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query", {"Name"}),
#"Transposed Table" = Table.Transpose(#"Removed Duplicates"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"house_number", Int64.Type}, {"road", type text}, {"city", type text}, {"postcode", type text}, {"country", type text}})
in
#"Changed Type"
in
AddressLookup
