data refresh to work, if other excel data source fails to refresh or is broken

[DeoYadav]

Hi,

If the PBI report is based on multiple data source then what could be the method for schedule refresh to work on the web service if other excel data source is broken or fail to refresh?

Appreciate your ideas!

Thank you, 

[Anonymous]

Hi,

Please paste the Advanced editor code on a blank query, This might be help.

If my answer helps, please accept the solution.

Regards 🙂

let

Path = "C:\Users\TR100722\Desktop\powerbi\Form.xlsx",

Result = try Binary.ToText(File.Contents(Path)),

Output =if Logical.From(Result[HasError]) then "not exist" else Excel.Workbook(File.Contents("C:\Users\TR100722\Desktop\powerbi\Form.xlsx"), null, true)

in

Output

[Greg_Deckler]

@DeoYadav Depends, you can refer to this: Dealing with errors - Power Query | Microsoft Learn. You could, for example, add a step or multiple steps that removes all errors in all columns. Thus, if there was an error involving data then this would not impact the loading of the table (it would likely load as a blank table for example) but not result in an error that would affect the other queries potentially. However, it is doubtful that the report would end up with valid information anyway.

[DeoYadav]

@Greg_Deckler thank you for sharing, will go through it and try if it works for me. in my case, I'm using excel data on the cloud and other data sources too.

thank you.

[Idrissshatila]

Hello,

You should use a data gateway if you have a data source that is on premises not on cloud, and you should setup the data gateway in the service. Also the server which has the datasets should be turned On when the data is refreshing.

Here is a link on data gateway https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[DeoYadav]

@Idrissshatila data is on the cloud in my case. thank you.