Forum Discussion
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
- v-juanli-msftCommunity Support
Hi fpennisi17
I can't reproduce your problem.
Could you check another possible solutions to the error "Formula.Firewall"?
https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- fpennisi17Helper III
Thank you, I'll try out the suggestions mentioned in that posts and let you know.