Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    hello Anonymous 

     

    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

     

     

     

  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    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

10 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

    • Jimmy801's avatar
      Jimmy801
      Community Champion
      6 years ago

      Hello Anonymous 

       

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

       

      Jimmy

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        hello Anonymous 

         

        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