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

Formula.Firewall error

Hi everyone, I need help with a power query in the editor. I have added a new column in a table with a web.content query where it gives me the error message Formula.Firewall error. here is the Code

 

Quelle = #"ARTICLE SALES",
        #"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"Modell", "Lieferantenfarbe", "Modellbezeichnung", "Product ID"}),
        #"Entfernte Duplikate" = Table.Distinct(#"Andere entfernte Spalten", {"Product ID"}),
        #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Entfernte Duplikate", "Img URL", 
            each Json.Document(
                    Web.Contents("https://hostname.app.de/getImageURLBySKU?sku="[Product ID])
                )),
        #"Erweiterte Img URL" = Table.ExpandRecordColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Img URL", {"result"}, {"Img URL.result"}),
        #"Umbenannte Spalten" = Table.RenameColumns(#"Erweiterte Img URL",{{"Img URL.result", "Img URL"}})
    in
        #"Umbenannte Spalten"

 

The result of what the query Json.Document(Web.Contents()) returns as a single query is

 

 

let
        Source = 
            Json.Document(
                Web.Contents("https://hostname.app.de/rest/unprotected/files/images/products/getImageURLBySKU?sku=[Product ID])
            ),
            
    imageURL = Source[result]
    in
        imageURL

 

 https://test.hostname.de/media/catalog/product/m/o/803040-107-1.jpg

I need the result of the Json. document for each individual Product ID in the new column

Anmerkung 2020-02-03 120043.png

Does anyone have an idea how I put the query Json.Document(Web.Contents()) outside the new query columns and get the result for each ID

2 ACCEPTED SOLUTIONS

hello @MarioBoehme 

 

okay, than these request has nothing to do with an Firewall-error. 

Change your query into a function and invoke it in a new custom column and passing the product id

Function name. 

 

//GetProductImage
(productid) =
let
        Source = 
            Json.Document(
                Web.Contents("https://hostname.app.de/rest/unprotected/files/images/products/getImageURLBySKU?sku=[" & productid & "])
            ),
            
    imageURL = Source[result]
    in
        imageURL

 

 

and in your table invoke it in a new column like this

 

GetProductImage( [Product ID])

 

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

 

 

View solution in original post

Hello @MarioBoehme 

 

I've just checked your error message again, and it seems that the error is caused due to different settings of your data sources. So please try to go go Datasourcesettings (Datenquelleneinstellungen) and set the same settings for every data source (public i suppose). This should solve your problem.

 

All the best

 

Jimmy

View solution in original post

10 REPLIES 10
v-juanli-msft
Community Support
Community Support

Hi @MarioBoehme 

As tested, i can't access your url.

Check if the articles help you.

https://blog.crossjoin.co.uk/2018/08/30/power-bi-extract-urls-web-page/

https://radacad.com/get-images-from-web-page-into-the-power-bi-report-using-power-query

 

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.

 

To better understand what I want. The result of the query returns the correct image URL.

 

let
        Source = 
            Json.Document(
                Web.Contents("https://hostname.app.de/rest/unprotected/files/images/products/getImageURLBySKU?sku=[Product ID])
            ),
            
    imageURL = Source[result]
    in
        imageURL

https://test.hostname.de/media/catalog/product/m/o/803040-107-1.jpg

 

I need the result of the query for each row of product ID in my article table. So I always get a different image URL as result for each Product ID. I don't know how to write this query correctly without getting the message Formula.Firewall error

hello @MarioBoehme 

 

okay, than these request has nothing to do with an Firewall-error. 

Change your query into a function and invoke it in a new custom column and passing the product id

Function name. 

 

//GetProductImage
(productid) =
let
        Source = 
            Json.Document(
                Web.Contents("https://hostname.app.de/rest/unprotected/files/images/products/getImageURLBySKU?sku=[" & productid & "])
            ),
            
    imageURL = Source[result]
    in
        imageURL

 

 

and in your table invoke it in a new column like this

 

GetProductImage( [Product ID])

 

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

 

 

Hi Jimmy, thanks for your suggestions. I did a small adjustment and the function works fine. 

(ProductSKU as text) =>
let
        Source = 
            Json.Document(
                Web.Contents("https://hostname.app.de/rest/unprotected/files/images/products/getImageURLBySKU?sku=" & (ProductSKU) )
            ),
            
    imageURL = Source[result]
    in
        imageURL

After this i put the function in my test table and it works fine for each Product ID

FunctionTableTest.png

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI1NDSxsDC2MI03MY53cQzyjnfyCXVVitWJVrKMMjUFypmYAOWM4n09XcBS8aHBri5g+ZB4Y5N4QwMDy/jixLSixDywYLB3vIFRvJGBgWF8Uk5icrZ+flpafHlGZkmqUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Product ID", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Geänderter Typ", "URLWithParameter(ProductID)", each IMGUrlwithParameterAsText([Product ID]))
in
    #"Hinzugefügte benutzerdefinierte Spalte1"

After this test, i have integrate the function in my origin Sales table an the error massage are displayed: Formula.Firewall: Abfrage 'ARTICLE SALES (2)' (Schritt 'Hinzugefügte benutzerdefinierte Spalte') greift auf Datenquellen mit Sicherheitsstufen zu, die nicht zusammen verwendet werden können. Erstellen Sie diese Datenkombination neu. 

 

Do you have an idea, how i get this to work for an existing query with an query from an folder.files

 

let
    Quelle = Folder.Files("R:\mycompany\bireporting\02-BI-DATA\\08-ARTICLE-SALES"),
    #"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"Content"}),
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(#"Andere entfernte Spalten", each [Attributes]?[Hidden]? <> true),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Datei transformieren"}),
    #"Erweiterte Datei transformieren" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", {"Modell"}, {"Datei transformieren.Modell"}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Erweiterte Datei transformieren", "IMGURL", each IMGUrlwithParameterAsText([Datei transformieren.Modell]))
in
    #"Hinzugefügte benutzerdefinierte Spalte"

 

Hello @MarioBoehme 

 

you have to put everything in one single query. This what I've suggested you in my first post

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Okay. Thank you @Jimmy801 . Please show me how to put everything into one query. It's not working for me.

 

//IMGUrlwithParameterAsText --------> this is my function
(ProductSKU as text) =>
        Source = 
            Json.Document(
     Web.Contents("https://ista.mf1832.de/ista/rest/unprotected/files/images/products/getImageURLBySKU?sku=" & (ProductSKU) )
            ),
    imageURL = Source[result]
        imageURL

let
//ArticleSalesQuery --------> this is my query

    Quelle = Folder.Files("R:\MyCompany\bireporting\02-BI-DATA\08-ARTICLE-SALES"),
    #"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"Content"}),
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(#"Andere entfernte Spalten", each [Attributes]?[Hidden]? <> true),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Datei transformieren"}),
    #"Erweiterte Datei transformieren" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", {"Modell"}, {"Datei transformieren.Modell"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Erweiterte Datei transformieren",{{"Datei transformieren.Modell", "Product ID"}}),
    #"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn(#"Umbenannte Spalten", "ImgUrl", each IMGUrlwithParameterAsText([Product ID]))
in
    #"Aufgerufene benutzerdefinierte Funktion"

 

Hello @MarioBoehme 

 

I've just checked your error message again, and it seems that the error is caused due to different settings of your data sources. So please try to go go Datasourcesettings (Datenquelleneinstellungen) and set the same settings for every data source (public i suppose). This should solve your problem.

 

All the best

 

Jimmy

MarioBoehme
Frequent Visitor

this query triggers the error message. 

#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Entfernte Duplikate", "Img URL", 
            each Json.Document(
                    Web.Contents("https://hostname.app.de/getImageURLBySKU?sku="[Product ID])
                )),

 If I set the privacy settings under options to ignore, the query works.

Hello @MarioBoehme 

 

I know, so if you want to make it work without changing this important setting, put everything in one query

 

Jimmy

Jimmy801
Community Champion
Community Champion

Hello @MarioBoehme 

 

You didn't post the exact firewall error but the Firewall.Error is triggered most proababy because you are referencing to another partition here:

Quelle = #"ARTICLE SALES",

Try to include this query into your main query, then it should work

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.