Getting Firewall issue with the power bi connection strings

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.