Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Getting Firewall issue with the power bi connection strings

Hi All,

 

I have a power bi report and all my dim and fact tables are connecting to an logic app with a http connection string along with some headers in it. Hence, the connection string is like this:

let
Source = Csv.Document(Web.Contents("https://prod------------------------------", [Headers=[unique_key="aaaaaa", file="aaaaaaa\aa\aaaaa\DimDate.csv", Enrollment=EnrollmentCall, Email=Email]]),[Delimiter=","])

followed by some transformations.

In the connection string - we have 4 parameters viz., unique_key (hardcoded), file (file path in a azure blob), Enrollment (enrollment number coming from referencing parameter enrollmentcall), Email (email parameter).

 

Now , this connection string is throwing the following error: 

in every connection string, there are 2 sources which are getting pointed to. One is Logic app connection which is anonymous authentication and second is Enrollment parameter(within in the header) authenticating with ACM connection.

Due to this, I am getting this firewall issue.

Can someone help me how to get rid of this error?

i have tried to choose "ignore all the privacy levels to the sources" in the Power BI options, it is only working with Desktop but not with Power BI Service.

I have also tried other workaround in google but they are also not working.

 

Can someone help me how to get rid of this error?

 

Thanks

Lakshmi Koduri

Lakshmi Koduri

Need Help

5 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous 

    That Source step on its own isn't causing the Formula Firewall error. I can use that exact code with another website and it retrives the data ok.

    You must have another step(s) that is referencing another data source, that's what's causing the FF error.

    The error message says the Removed Duplicates1 step is causing the error.  Can you share th actual query code?

    You need to split the query into parts with only 1 data source in each query.

    https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Yes Philip, I have tried with the solution in the document which you have referred. Its not working for my issue. Tell u why.

      There is a parameter which is referencing another query of Data Source 2. But this parameter is insiders headers of the connection string of Data Source 1, which is why I am not able to split the query. So it is basically like all the web contents is from Query1 and a parameter inside the Headers of Query1 web connection is from another Query2.

      Query1(------, (headers[parameter from query2). How can I split this query now?

      • PhilipTreacy's avatar
        PhilipTreacy
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous 

        Setup2 queries.

        In your first query just have it so that it contains the website URL and any other data you need to make the request, but don't make the Web.Contents request in this query.

        In the second query, do whatever you need to get the parameter value. Create the Headers record, then convert to a table.

        Merge the 2 queries as new.

        The new merged query contains the information you need to make the Web.Contents request but only accesses 1 data source.

        Here's my example PBIX file showing the process with dummy data sources, you will of course need to adapt your queries.

         

        Query 1

        let
    Source = "https://www.bbc.co.uk",
    #"Converted to Table" = #table(1, {{Source}}),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

         

        Query 2

        let
    abc = Csv.Document(Web.Contents("https://www.wikipedia.org"),[Delimiter=","]),
    Step2 = [Headers=[unique_key="aaaaaa", file="aaaaaaa\aa\aaaaa\DimDate.csv", Enrollment=abc, Email="Email"]],
    #"Converted to Table" = Record.ToTable(Step2),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type)
    
in
    #"Added Index"

         

        Merged Query

        let
    Source = Table.NestedJoin(Query1, {"Index"}, Query2, {"Index"}, "EC", JoinKind.LeftOuter),
    #"Expanded EC" = Table.ExpandTableColumn(Source, "EC", {"Value"}, {"Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded EC", "Custom", each Csv.Document(Web.Contents([Column1], [Value]),[Delimiter=","]))
in
    #"Added Custom"

         

        Regards

        Phil

        If I answered your question please mark my post as the solution.
        If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.