cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarianneElvers
Regular Visitor

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

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:

Source error.png

 

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

Source error2.png

 

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

1 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

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Hi @MarianneElvers ,

 

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

5.png6.png

 

 

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.

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
Regular Visitor

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

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

View solution in original post

Hi Pete,

 

Thanks a lot, it works! 

 

KR Marianne

BA_Pete
Super User
Super User

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors