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 ...
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

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
Icon for Community Champion rankCommunity 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

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    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"

     

    • Jimmy801's avatar
      Jimmy801
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hello Anonymous 

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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"