Forum Discussion

tkboisvert's avatar
tkboisvert
Helper II
6 years ago

Multi Source Data Combination on a Row-by-Row basis

Hi all-

 

I'm making calls to the ClubHouse API in PowerQuery.

 

The way it works, I can only see details for sub-dimentions if I specifically call them.

 

For example, I can see all Stories IDs attached to a project ( API.com/projects ), but I can only see the detail level of a specific Story if I request that story specifically ( API.com/Stories/57 ). This gets more complex as you get to the comment layer... 

 

Anyways, I've got a function query that calls the api at a specific point and works great in PBI desktop.

 

Source = #"ClubHouse API Call"("projects")

 

This query creates my base query, projects. Then, in a separate query I add a column that executes the same function against that column.

 

#"Invoked Custom Function" = Table.AddColumn(Source, "Stories", each #"ClubHouse API Call"("projects"&[Story List]))

The new column runs an API call against a specific Project&StoryID and returns a JSON object that I can parse out beautifuly.

 

The issues

 

Infrequently, I get an error that says: Formula.Firewall: ..... references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

That was fixed by changing to the following privacy settings:

 

 

The real problem comes with refreshing online, I receive the following message:

 

Underlying error code:-2147467259 Table: Stories.
Underlying error message:[Unable to combine data] Section1/Stories/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
  
  
Microsoft.Data.Mashup.MashupSecurityException.Reason:PrivacyError

 

I tried to use a data-gateway but got the same result. Both 'datasets' are coming from the same function and I've disabled all Privacy settings, what should I try next?

 

 

Need Help

2 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi tkboisvert,

    I find a similar post for Combine data  from different datasource, you could refer to Power Query Errors: Please Rebuild This Data Combination for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • tkboisvert's avatar
      tkboisvert
      Helper II
      6 years ago

      This post refers to splitting the datasets by staging.

       

      I am adding a dataset row by row. I'm adding a column that is executing a PQ function, calling the API row by row. So I cannot split up the dataset.

       

      I've heard that making a Custom Connector is one way to eliminate this. Is that so?