All links (be it formula data connection etc) only can evaluate when the workbook opens. Hence, the issue.
There's no way to force evaluation on closed workbook.

AFAIK, there are no workaround for this, workbook that serves as intermediate step must be opened, before final workbook can refresh with current data.
Though PowerBI desktop does work through intermediary Excel to refresh data from source... this isn't available for Excel version of PowerQuery/Get & Transform.