Formula.Firewall Error

[LP2803]

Hi Team,

I have a problem with API call from passing data from one query to another through parameter.

Query1:

let
Source = Json.Document(Web.Contents("https://api.onguard.convergeone.com:8080/cc2076video/videoCDR/_search?q=@timestamp%3A[2019-10-01T00:...")),
hits = Source[hits],
hits1 = hits[hits],
#"Converted to Table" = Table.FromList(hits1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_index", "_type", "_id", "_score", "_source"}, {"Column1._index", "Column1._type", "Column1._id", "Column1._score", "Column1._source"}),
#"Expanded Column1._source" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1._source", {"@timestamp"}, {"Column1._source.@timestamp"}),
#"Sorted Rows" = Table.Sort(#"Expanded Column1._source",{{"Column1._source.@timestamp", Order.Descending}})
in
#"Sorted Rows"

Query2:

let
Source1 = Json.Document(Web.Contents("https://api.onguard.convergeone.com:8080/cc2076video/videoCDR/_search?q=@timestamp%3A[2019-10-01T00:...")),
Source = Json.Document(Web.Contents("https://api.onguard.convergeone.com:8080/cc2076video/videoCDR/_search?q=@timestamp%3A["& Parameter1 &"%20TO%20now/d]&size=10000")),
hits = Source[hits],
hits1 = hits[hits],
#"Converted to Table" = Table.FromList(hits1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_index", "_type", "_id", "_score", "_source"}, {"Column1._index", "Column1._type", "Column1._id", "Column1._score", "Column1._source"}),
#"Expanded Column1._source" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1._source", {"@timestamp"}, {"Column1._source.@timestamp"}),
#"Sorted Rows" = Table.Sort(#"Expanded Column1._source",{{"Column1._source.@timestamp", Order.Ascending}})
in
#"Sorted Rows"

[LP2803]

Here is the error 

[v-yingjl]

Hi @LP2803 ,

Sorry for replying late. Based on the error message, it produces folding in the query. The purpose of the Data Privacy Firewall is  to prevent Power Query from unintentionally leaking data between sources.

As part of folding, PQ sometimes may determine that the most efficient way to execute a given mashup is to take data from one source and pass it to another.

You may try to make these data sources' pravicy level is same or ignore the pravicy level in Options and settings.

In addition, you can also refer these simliar issues and blogs that could help you:

1. Formula.Firewall: Query references other queries or steps, so it may not directly access a data sour 
2. Formula.Firewall: Query references other queries, so it may not directly access a data source. 
3. Behind the scenes of the Data Privacy Firewall 
4. Data Privacy and the Formula Firewall in Power BI / Power Query 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LP2803]

@v-yingjl thanks for your response. I infact tried disabling the firewall and it works. However, the problem would recurr when I publish the pbix file to Powerbi Workspace, it gives the same error when i schedule it for data refresh

[v-yingjl]

Hi @LP2803 ,

Have you used gateway when configuring refresh and wheter it has been configured correctly?

As I posted previously, you can also check the privacy levels for gateway if you are using it, the privacy level of all data sources should be the same.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LP2803]

Hi v-yingjl,

Do we need gateway for the APIs?  if Yes, could you please guide me how to use gateway for this API?

[v-yingjl]

Hi @LP2803 ,

When you configure gateway, please try to use the web as the data source type and enter your api url.

In addition, I have noticed your other posts that you have issues about the authentication method, you can refer this similar issue and blog that could help you:

1. Power BI Service - Refresh of data on API using POST method 
2. Call an Azure Function from Power BI 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.