cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brmdkkr
Frequent Visitor

Formula.Firewall error; calling a web api get's error when used in a table with append other tables.

Hi,

 

I've got a sheet in Power BI that consists of several merged sheets.
Now, I want to run a web api( the HERE geocoder api) on the final sheet but I get the error

please rebuild this data combination
I've followed the instructions here and here.
I've also tried defining and invoking the function within the query likehere .
Could it be that I have to define the query for every 'source' sheet which is used to merge for the final sheet?
All sources have to have an Organizational privacy level, other wise stuff breaks, so turning the whole privacy level checks off is not doable.

Thanks in advance,
Bram

1 ACCEPTED SOLUTION

Hi,
I've fixed the issue by using the Relative path option.
My function now looks likes this:
= (coding as text) =>
let
    Bron =  Json.Document(Web.Contents("https://geocoder.api.here.com/6.2?app_id=xxxxxxxxxx&app_code=xxxxxxx", [RelativePath="geocode.json", Query=[searchtext="coding"]]))
   in
Bron

I hope this helps other people that run into the same problem 🙂

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Brmdkkr ,

I'm afraid that you'd better show the sample code which could reproduce your scenario so that we could help further on it.

In addition, @ImkeF  and @MarcelBeug  may have other ideas.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,
I've fixed the issue by using the Relative path option.
My function now looks likes this:
= (coding as text) =>
let
    Bron =  Json.Document(Web.Contents("https://geocoder.api.here.com/6.2?app_id=xxxxxxxxxx&app_code=xxxxxxx", [RelativePath="geocode.json", Query=[searchtext="coding"]]))
   in
Bron

I hope this helps other people that run into the same problem 🙂

View solution in original post

My (shortend)query looks like this:

let
geocode = (herecoding as text) =>
let
coding = herecoding,
PostContent=””,
Bron = Json.Document(Web.Contents(“https://geocoder.api.here.com/6.2/geocode.json?app_id=xxxx&app_code=xxxx&searchtext=”&coding&"", [Headers=[#”Accept-Encoding”=”gzip”]]), Compression.GZip),
#”Geconverteerd naar tabel” = Record.ToTable(Bron),
in
#”Geconverteerd naar tabel” ,
Source = Excel.Workbook(Web.Contents(“https://vanbruggen.sharepoint.com/sites/ManagementInformatie/Gedeelde%20%20documenten/Excel 2.0/Afspraken 2018 2.0 (nieuw).xlsx”), null, true),
Blad1_Sheet = Source{[Item=”Blad1″,Kind=”Sheet”]}[Data],
#”Headers met verhoogd niveau” = Table.PromoteHeaders(Blad1_Sheet, [PromoteAllScalars=true]),
#”Query toegevoegd” = Table.Combine({#”Headers met verhoogd niveau”, Afspraken2017, #”Blad1 (2)”}),
#”Rijen gefilterd” = Table.SelectRows(#”Query toegevoegd”, each ([Jaar] 2019)),
#”Query toegevoegd1″ = Table.Combine({#”Rijen gefilterd”, #”Rapportage Inbound 0800″}),
#”Kolommen verwijderd” = Table.RemoveColumns(#”Query toegevoegd1″,{“Column50”, “Column51”, “Column52”, “Column53″}),
#”Type gewijzigd” = Table.TransformColumnTypes(#”Kolommen verwijderd”,{{“Beldatum”, type date}}),
#”Aangepaste kolom toegevoegd” = Table.AddColumn(#”Type gewijzigd”, “DatumKey”, each Date.ToText([Beldatum],”yyyyMMdd”)),
#”Filtered Rows” = Table.SelectRows(#”Aangepaste kolom toegevoegd”, each true),
#”Renamed Columns” = Table.RenameColumns(#”Filtered Rows”,{{“CTPID”, “Afspraken”}}),
#”Type gewijzigd1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“Huisnummer”, type text}, {“Postcode”, type text}}),
in
#”Type gewijzgd1"

 

I've also tried to define the function within every query that is merged into this one but that aslo didn't help.
My next step is to use Relative Path and Query to improve the query but maybe some people have other ideas?

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors