How can I switch data source in case of failure automatically using on premise gateway cluster?

Hi nihilistdbanana, 

I'm not sure this is possible from the gateway. I think you'd need to set something up upstream of the gateway that handles the DR. 

In my environment, we use CNAMEs for our SQL server instances, so if something needs to fail over, we update the CNAME and then all connections are automatically switched. I'm no expert at storage management, but I wonder if something similar is possible at the storage layer? 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.  

Hi nihilistdbanana ,

Power Query in Power BI supports conditional logic, allowing you to try loading data from one file path and fallback to another if the first one fails. You can use try, otherwise in M language to achieve this.
This code first attempts to load the file from the PROD server. If it fails for example, if the file doesn't exist or is inaccessible, Power BI will automatically try to load the file from the DR server. It is simple and self-contained solution within Power BI. No need for external infrastructure changes. There might be a slight delay when checking both paths. Additionally, this solution works for individual files and not the entire dataset if you're dealing with multiple files.

let
prodPath = "\\prodserver\files\myfile.csv", // PROD server path
drPath = "\\drserver\files\myfile.csv", // DR server path
source = try Csv.Document(File.Contents(prodPath)) otherwise Csv.Document(File.Contents(drPath))
in
source

Hope this helps.
Thank you