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

[fpennisi17]

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

[v-juanli-msft]

Hi @fpennisi17 

I can't reproduce your problem.

Could you check another possible solutions to the error "Formula.Firewall"?

https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-form...

https://blog.crossjoin.co.uk/2017/07/04/data-privacy-settings-in-power-bipower-query-part-4-disablin...

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.

[fpennisi17]

Thank you, I'll try out the suggestions mentioned in that posts and let you know.