Forum Discussion
Cannot exclude broken connections from refresh
Hi Taylor,
Ok, so there's two parts to this: 1) holding a static version of your data to use until the link is fixed, then 2) dynamically switching to the 'correct' data should the link become unbroken.
1) For this part, I would recommend loading your archive data into a Dataflow from whichever link currently works. You only need to connect and refresh once, then don't schedule any further refreshes on this Dataflow so it remains in a static state but also easily available to all reports.
2) In each of your queries, you should reference both sources (archive Dataflow and maybe-unbroken-link-source) and dynamically switch between them based on an "if desired link still broken then use Dataflow" evaluation.
Chris Webb covers this really well in the following link. Just make sure to also read the comments on the post as Ken Puls adds a key update to the original code presented by Chris:
https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/
Pete
Hi Taylor,
Ok, so there's two parts to this: 1) holding a static version of your data to use until the link is fixed, then 2) dynamically switching to the 'correct' data should the link become unbroken.
1) For this part, I would recommend loading your archive data into a Dataflow from whichever link currently works. You only need to connect and refresh once, then don't schedule any further refreshes on this Dataflow so it remains in a static state but also easily available to all reports.
2) In each of your queries, you should reference both sources (archive Dataflow and maybe-unbroken-link-source) and dynamically switch between them based on an "if desired link still broken then use Dataflow" evaluation.
Chris Webb covers this really well in the following link. Just make sure to also read the comments on the post as Ken Puls adds a key update to the original code presented by Chris:
https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/
Pete
Hi Pete,
Thanks for taking the time to look into this, I think this will probably be the smoothest way to address the issue and future proof the current system.
Thanks again!
Taylor
