If first source fails a few steps in, then choose another source

[MarianneElvers]

Hi,

I have a report that often fails to refresh due to a Google Sheet that often fails to deliver the data.

Data from this feed is integrated in the datamodel, and a lot of metrics depend on that feed.

By saying that the sheet fails to deliver the data, I mean that the sheet and the specific tab is there, but it contains an error message instead of data. However, this error is only visible when I navigate to that specific tab (the tab containing the data that I need), and therefore the error is not visible initially, when loading the first source step.

Error a few steps into the Google Sheet Query:

Error message when arriving to the final step of the Google Sheet query:

I have created a fall-back table (csv file), containing a snapshot of the Google Sheet that is a few days old. 

Is there a way to detect if there is an error of the first source (Google Sheet), either the error a few steps in (as in the first screendump), or the error that appears at the final step (the second screendump), and then to switch to the second source (csv)?

Any help is highly appreciated!

KR Marianne

[v-stephen-msft]

Hi @MarianneElvers ,

You can clear your cache, go to "data source settings" and clear the related permission - then recapture  them.

Reference: https://www.c-sharpcorner.com/article/how-to-change-data-source-of-existing-report-in-power-bi/

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarianneElvers]

Hi Stephen,

Thank you for your reference.

What I am looking for is an automatic fall back operation to a second source if the first source fails after certain steps.

If I understand this post correctly, this is a manual operation of selecting a different source.

KR Marianne

[MarianneElvers]

Hi Pete, 

Thanks for the excellent references.

I have tryed to apply the first without success, as my case differs somewhat, as the feed itself does not fail, only when navigating to the right step. 

As I don't know how all the (hundreds of) columns look like, I don't see how I can apply the second reference either.

If you know a "switch" between entire sources is possible if step x is failing, I would highly appreciate your help.

KR Marianne

[BA_Pete · Accepted solution]

Hi @MarianneElvers ,

I think the first link I sent should contain the correct principle to do what you need, it will just be about how you set up the source blocks.

For example, I think your code should be structured something like this for it to work:

let
  Source_A = Google Docs connection String,
  Navigation_A = Steps to navigate to correct file and sheet,
  Preparation_A = Steps to prep data to usable starting point e.g. Promote Headers etc,

  Source_B = Alternative connection string,
  Navigation_B = Alt nav steps,
  Preparation_B = Alt prep steps,

  testForError = try Preparation_A,

  Output = if testForError[HasError] then Preparation_B else Preparation_A

in
  Output

This should catch if ANY step in the Source_A block resolves to an error state, and switch to the Source_B block.

Pete

[MarianneElvers]

Hi Pete,

Thanks a lot, it works! 

KR Marianne

[BA_Pete]

Hi @MarianneElvers ,

There's probably too much to cover within a forum reply here, but here are a couple of great resources from Chris Webb's blog that I think will give you just what you need:

https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/ 

**NOTE: make sure to read the comments at the end of the above blog, as Ken Puls highlights some issues he encountered and how he fixed them.

https://blog.crossjoin.co.uk/2015/07/03/ensuring-columns-are-always-present-in-a-table-returned-by-p... 

Pete