Cannot exclude broken connections from refresh

[TaylorH]

Hi all,

I have a Power BI dashboard that draws on datasets from several sources. Our team is having trouble accessing a number of these datasets due to trouble with CRM access, so our solution has been to house data elsewhere when it requires continuous updating, but for data that does not require updating, I have just been using the historical pre-loaded data held in the PBI file from when the connection was working.

I now want to set up the queries so that clicking Refresh will only refresh the 15-20 data tables housed elsewhere while ignoring those archived files with the broken connection  -- since including these will cause the whole refresh fail like so:

To replicate this, I have attached an example file which calls on two datasets:

1. Working Connection -> local csv which connects correctly
2. Broken Connection -> local csv which initially connects correctly, but breaks due to a change in the subfolder name

LINK: https://drive.google.com/drive/folders/1TFelWa3OmuLTtI4cAnUoMe9Vvxuxw4KX?usp=sharing

NOTE: you will need to add these two datasets to the PBI file on your local device, then close the PBI file and change the subfolder name like so:

Refreshing now produces an error:

In Power Query, I had hoped that a solution to this would be to uncheck 'Include in refresh report' (as below), however I am unable to apply this change without having a working connection to begin with, leaving me unable to refresh it and unable to exclude it from refresh. Is there a workaround to resolve this?

So far the solutions I see are:

1. Turn off 'Enable load' - This will remove the whole data source from the model, breaking model connections and any visuals calling on this data (Not a solution)
2. Manually refresh each of the 15-20 functioning data tables with each update (time consuming and hard to explain to other users who are not familiar with Power BI)
3. Extract each archived data table into a csv housed in the new working location, then change the initial 'Source' step of each of these to pull from this new location. (Could work, though there are approx. 40 of these tables and I will lose the ability to revert back to the CRM connection if that connection is ever repaired)

Is there a better way here that I am not seeing? Really appreciate any help with this!

Many thanks,

Taylor

[BA_Pete · Accepted solution]

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

[TaylorH]

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