Forum Discussion

fpennisi17's avatar
fpennisi17
Helper III
6 years ago

Formula.Firewall error triggered when calling web service with current timestamp

Hi,

I'm trying to retrieve data from a public web service which requires two dates (from and to) as parameters.

Every time the query run, I want to retrieve data from the web service in the time window UtcNow - 365 days.

 

This is the code I am currently using:

 

let
to = DateTimeZone.UtcNow(),
from = to - #duration(365,0,0,0),
Origine = Dighe,
#"Rimosse altre colonne" = Table.SelectColumns(Origine,{"Latitudine", "Longitudine"}),
#"Calcolata WebRequest" = Table.AddColumn(#"Rimosse altre colonne", "WebRequest", each "http://webservices.ingv.it/fdsnws/event/1/query?" & "starttime=" & DateTimeZone.ToText(from, "yyyy-MM-ddT00:00:00") & "&endtime=" & DateTimeZone.ToText(to, "yyyy-MM-ddT00:00:00") & "&minmag=" & Text.From(MinMagnitude, "en-US") & "&lat=" & Text.From([Latitudine], "en-US") & "&lon=" & Text.From([Longitudine], "en-US") & "&(maxradiuskm=" & Text.From(200, "en-US") & ")"),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Calcolata WebRequest", "Events", each Xml.Tables(Web.Contents([WebRequest])))
in
#"Aggiunta colonna personalizzata"

 

It triggers the Formula.Firewall exception, asking to rebuild the query combination. I guess this is due to the fact that I am calculating from and to with two starting from the DateTimeZone.UtcNow() function.

I tried several refactoring of the code, also taking into account the reccomendations of this article https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ but I still got that error.

 

Can someone suggest me an alternative?

Thank you

2 Replies