Getting Firewall issue with the power bi connection strings

[Lakshmi_Koduri]

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

[PhilipTreacy]

Hi @Lakshmi_Koduri 

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.

[Lakshmi_Koduri]

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]

Hi @Lakshmi_Koduri 

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.

[Lakshmi_Koduri]

Hi Philip,

I have tried the way you recommended, but it is throwing me the error at the custom add step in merged query. This error is there in your query too.

[PhilipTreacy]

Hi @Lakshmi_Koduri 

The error saying unique_key  isn't a valid option?  None of those header parameters are valid.  I assumed you would work that out as that is how you supplied the original query.

Maybe you need to supply those parameters as Content rather than Headers?  Sory I don't know as I don't have your full query or know exactly what you are trying to do.

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.