Scheduled refresh problem (joining sources), need help understanding the error message.

[Bamse]

I keep on getting a refresh error when performing a scheduled refresh, and need help to understand what the error message really means.

The error is:

Data source error{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"[Unable to combine data] Section1/TableWithRefreshProblem/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}}],"exceptionCulprit":1}}} Table: TableWithRefreshProblem.

Note: The message does not refference a specific step as seems to be common in similar errors found all over the web.

As far as I can tell it's caused by combining the data from what Power-BI believes to be two different 'sources'.
The sources are in fact different pages from the same web site.

I've been changing the formulla firewall settings to 'None', 'Private' and 'Organizational' and back and forth but that does not seem to resolve the issue.
I've been creating separate tables that store the results of queries since I found some web sites suggesting this would solve similar problems, but no luck here either.

The worst part is that I am slowly tearing appart my carefully crafted model just to make the refresh work, but I don't get any further.
Note that as soon as I stop merging the tables, the model refreshes, but the model is not usable without the merge.

Simplified chain of events:
Table1 =
let
Source1 = Json.Document(Web.Contents("https://website.com/Search.aspx?output=json", [Query = [query="fruits"], Headers = [Authorization = Bearer]])),
Source2 = Json.Document(Web.Contents("https://website.com/Search.aspx?output=json", [Query = [query="animals"], Headers = [Authorization = Bearer]])),
Source3 = Json.Document(Web.Contents("https://website.com/Search.aspx?output=json", [Query = [query="flowers"], Headers = [Authorization = Bearer]])),

...
Processed = Processing(Source1 + Source2 + Source3 ... ) // This goes fine
in
Processed

Table2 =
let
Source = Json.Document(Web.Contents("https://website.com/RawData.aspx?output=json", [Query = [query="length of a rope"], Headers = [Authorization = Bearer]])),
Processed = Processing(Source)
in
Processed

So far so good... now it breaks when I combine Table1 and Table2:

Table3 =
let
Source = Table.NestedJoin(Table1, {"keycolumn1"}, Table2, {"keycolumn2"}, "MergedData", JoinKind.LeftOuter)
in
Source

[edhans]

I think your problem is table 1. Break that into 3 distinct tables, then use Table.Combine (Append) to combine them.

THere are issues with the service merging tables when you get clever with the source lines.

For example, if you create one table that is a source to a SQL database, then 2 reference quereis that database then each select a table from that single line source, then you merge either of those with another data source, like SharePoint, it works great in Power BI desktop, but will not work in the service. 

[Bamse · Accepted solution]

I've managed to get the refresh working

The solution was actually rather different than the direction I was working in.
Instead of splitting up all queries into sepparate tables and loading them, I've placed everytihing in a single query.
This is rather ugly but makes the refresh work.
I am now working on breaking up the code into functions to make it readable again, until something breaks again...

TIL (today I learned):
One should test scheduled refresh from the start of development and keep testing for every noteworthy change in architecture.