cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Formula.Firewall: Please rebuild this data combination.

Hi all,

 

I have a table contains some values and I need to do a API call for each item with some conditions.

I have trouble with this error:

"Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

 

I have read this https://insightsquest.com/2017/10/19/data-source-staging-queries/ and this https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ but I'm still stuck. I have also tried to ignore privacy settings but it worked only on Power BI desktop. I have still the errors when refreshing online.

 

I hope someone can help me.

Here is my code. I have kind of anonymised it, I hope there is no typo. But it is working fine if I replace the query with a fixed list or if I ignore the privacy setting.

listingItems query:

let
    myTable = Table.Distinct( Table.SelectColumns(otherTable, { "key", "filteringValue"})),
    filteredSource = Table.SelectRows(myTable , each ([filteringValue] = "KEYWORD")),
    myList = filteredSource[key]
in
    myList 

 

apiCall query:

let
    //calling a another query instead of creating the list here:
    myItemList = listingItems 
   
	// function to do one call per item
    FnGetOneItem = (itemId) as table =>
		let
			myKey = "xxx",
			pageLimit = 2,          
			myPageSize= 2,
			order = "-modificationDate",
			
			HTTPHeader = [RelativePath = itemId & "/query", Query = [pageSize = Number.ToText(myPageSize), page = Number.ToText(pageId), sort = order], Headers = [#"Ocp-Apim-Subscription-Key" = apimKey ]],
			Source = Json.Document(Web.Contents("https://url.com", HTTPHeader)),
			values = try Source[values] otherwise null,
			results = [Values=values]
		in
			results,

        data = List.Generate(
            () => [pageId=1, result = FnGetOnePage(pageId)],
            each [pageId]<= pageLimit and [result][Values] <> {},
            each [pageId=[pageId]+1, url=urlroot & Number.ToText(pageId), result = [result] & FnGetOnePage(pageId) ]
        ),

        output = Table.FromRecords(data)
    in output,

    Source = List.Generate(
        () => [position=0, storeId = myItemList{position}, outputTable = FnGetOneItem(myItemList{position})],
        each [position] < List.Count(myItemList),
        each [position=[position]+1, storeId = myItemList{position}, outputTable = FnGetOneItem(myItemList{position})]
    ),

    Final = Table.FromRecords(Source)
in
    Final 

 

Thank you in advance for any help.

Brice

9 REPLIES 9
Highlighted
Super User V
Super User V

Re: Formula.Firewall: Please rebuild this data combination.

Hi @Brice_LE-LANN ,

 

Try writing the code in only one query. I had the same problem few weeks ago and it worked for me.

You can create a function to call the API, but handle the tables together.

 

Avoid code like:

myTable = Table.Distinct( Table.SelectColumns(otherTable, { "key", "filteringValue"})),

or

 

 myItemList = listingItems 

It's passing the tables/list from others queries, write them together. 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted
New Member

Re: Formula.Firewall: Please rebuild this data combination.

Hi @camargos88 

 

thanks for the reply, much appreciated.

I have tried to put it in one query but it gave me the same output.

let
    //creating my list in one query:
    myItemList = Table.SelectRows(Table.Distinct( Table.SelectColumns(otherTable, { "key", "filteringValue"})), each ([filteringValue] = "KEYWORD"))[key], 
   
	// function to do one call per item
    FnGetOneItem = (itemId) as table =>
		let
			myKey = "xxx",
			pageLimit = 2,          
			myPageSize= 2,
			order = "-modificationDate",
			
			HTTPHeader = [RelativePath = itemId & "/query", Query = [pageSize = Number.ToText(myPageSize), page = Number.ToText(pageId), sort = order], Headers = [#"Ocp-Apim-Subscription-Key" = apimKey ]],
			Source = Json.Document(Web.Contents("https://url.com", HTTPHeader)),
			values = try Source[values] otherwise null,
			results = [Values=values]
		in
			results,

        data = List.Generate(
            () => [pageId=1, result = FnGetOnePage(pageId)],
            each [pageId]<= pageLimit and [result][Values] <> {},
            each [pageId=[pageId]+1, url=urlroot & Number.ToText(pageId), result = [result] & FnGetOnePage(pageId) ]
        ),

        output = Table.FromRecords(data)
    in output,

    Source = List.Generate(
        () => [position=0, storeId = myItemList{position}, outputTable = FnGetOneItem(myItemList{position})],
        each [position] < List.Count(myItemList),
        each [position=[position]+1, storeId = myItemList{position}, outputTable = FnGetOneItem(myItemList{position})]
    ),

    Final = Table.FromRecords(Source)
in
    Final 

Thank you again, and if you have any idea, I'll take it

Highlighted
Super User V
Super User V

Re: Formula.Firewall: Please rebuild this data combination.

@Brice_LE-LANN ,

 

Try changing this part:

 

 myItemList = Table.SelectRows(Table.Distinct( Table.SelectColumns(otherTable, { "key", "filteringValue"})), each ([filteringValue] = "KEYWORD"))[key], 

 

It receives other table "otherTable", try querying this table in the same query as well.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted
New Member

Re: Formula.Firewall: Please rebuild this data combination.

@camargos88 

 

I am sorry but I don't understand. By what should I replace it?

I need to query this table to get the list of items.

 

Thanks,

Brice

Highlighted
Super User V
Super User V

Re: Formula.Firewall: Please rebuild this data combination.

@Brice_LE-LANN ,

 

Do not refer it on the other query, copy the code that queries it from the source.

Try writing the query without dependencies.

 

You can use disable the parallel load tables for performance issues, so it will query just once.

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted
New Member

Re: Formula.Firewall: Please rebuild this data combination.

@camargos88 

(thanks for the reactivity)

 

My "otherTable" is exactly extracted from API calls. To be clear, I have:

- one query which calls an API (get Items), extract some values. (I do some graphs on those value)

- for some of those items, I want to do another API call to get different data

 

You advise me to:

- one query which calls an API (get Items), extract some values. (I do some graphs on those value)

- create another query which will call the same API, and for each of the value, do another call.

 

So I do not have depencies (but I call the first API twice).

 

Is that correct?

Highlighted
Super User V
Super User V

Re: Formula.Firewall: Please rebuild this data combination.

@Brice_LE-LANN ,

 

Can you share your pbix without the key ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted
New Member

Re: Formula.Firewall: Please rebuild this data combination.

I am afraid I can't, there is too much data to hide.

I can share the first query though:

let
    myKey = "xxx",
    pageLimit = 2,          
    myPageSize= 2,
    order = "-modificationDate",

    FnGetOnePage = (pageId) as record =>
    let 
        HTTPHeader = [Query = [pageSize = Number.ToText(myPageSize), page = Number.ToText(pageId), sort = order], Headers = [#"Ocp-Apim-Subscription-Key" = apimKey ]],
		Source = Json.Document(Web.Contents("https://url.com", HTTPHeader)),
        count = try Source[count] otherwise null,
        values = try Source[values] otherwise null,
        results = [Count=count, Values=values]
    in
        results,

    data = List.Generate(
        () => [pageId=1, result = FnGetOnePage(pageId)],
        each [pageId]<= pageLimit and [result][Values] <> {},
        each [pageId=[pageId]+1, result = [result] & FnGetOnePage(pageId) ]
    ),

    output =  Table.FromRecords(data),
    allValues = Table.ExpandRecordColumn(output, "result", {"Count", "Values"}, {"result.Count", "result.Values"}),
    expendedAllValues = Table.ExpandListColumn(allValues, "result.Values"),
    #"Expanded result.Values" = Table.ExpandRecordColumn(expendedAllValues, "result.Values", {"key", "filteringValue"}, {"key", "filteringValue"})
    
in
    #"Expanded result.Values"
Highlighted
Super User V
Super User V

Re: Formula.Firewall: Please rebuild this data combination.

@Brice_LE-LANN ,

 

That's difficult to say without look to the references.

However the problem is when you pass a value from one query to another.

That's why you have to write the code querying everything in the same query.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors