Hi All,
I've created a custom function (shown at the bottom of this message) in Power BI Desktop which uses the Google geocoding API to calculate latitude and longitude for a provided address.
I've then created a custom column to invoke this function for the address field in every row of the address table of a membership database:
=fngeocode(([addPostCode])
The issue I'm having is that that this membership database is live and is being added to every day with new sign ups and to import and geocode these new membership addresses I need to refresh the data in Power BI: this causes the custom column to re-invoke the custom function for every row in the address tables (even for rows that have already been geocoded).
As the address table is around 25,000 rows long, this results in that number of requests to the Google Geocoding API every time I want to refresh the data or even make slight modifications to the query: something that takes both a significant period of time and means that I'm going far above the free limits to google's API service.
What I want is to only invoke the custom function for new rows that haven't yet been geocoded (i.e. where the latitude and longitude columns are blank) but I don't know how to achieve this. Does anyone have any ideas?
My only thoughts were to change the custom column code to only invoke the function on blank (not yet geocoded cells) but I don't know whether this is possible since it would involve circular references.
As I say, any help or advice on this would be very much appreciated!
For reference, here's my custom function code:
let
getgeo = (postcodeb as text) =>
let
Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/geocode/json?address="&postcodeb&",UK&key=MYKEY")),
results = Source[results],
results1 = results{0},
geometry = results1[geometry],
location = geometry[location],
#"Converted to Table" = Record.ToTable(location),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
TestForError = try #"Promoted Headers",
Output = if TestForError[HasError] then null else #"Promoted Headers"
in
Output
in
getgeo
