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,
I prepared function for translating latitude and longitude to address (street, city, house number and so on...). Everything is almost OK but sometimes I get an error because house_number is unavailable but my function require it:
How can I set that this column can be empty?
TY
Solved! Go to Solution.
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
In the image the formula is only partialy visible.
Can you copy paste the entire formula?
My function:
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")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"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
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
Thank you!
I have an error - The remote server returned an error: (429) Too Many Requests.
Maybe do you know how to solve it?
You are using a free service, so limitation exists on their site.
https://operations.osmfoundation.org/policies/nominatim/
Requirements
No heavy uses (an absolute maximum of 1 request per second).
Provide a valid HTTP Referer or User-Agent identifying the application (stock User-Agents as set by http libraries will not do).
Clearly display attribution as suitable for your medium.
Data is provided under the ODbL license which requires to share alike (although small extractions are likely to be covered by fair usage / fair dealing).
How to set 1 req per sec?? Is it possible?
You can add delays, this is already explained in the following topic:
https://community.powerbi.com/t5/Integrations-with-Files-and/API-call-limit-introduce-delay/td-p/362...
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |