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

[tkboisvert]

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?

[dax]

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]

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?