cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bamse
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Bamse
Frequent Visitor

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.

View solution in original post

2 REPLIES 2
edhans
Super User III
Super User III

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Bamse
Frequent Visitor

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors