Datasource Selection based on PBIX refresh location

[kevinht]

Hi,

I'm developing a pbix outside of its use environment and I have no permissions to connect to the datasource under my user. So I'm having to email the report to a user with credentials to hit refresh to obtain new data and then they would email it back. This makes it a pain for testing when the data I need isn't there or requires curating.

Is there anyway for a PQ Query to attempt to use one datasource and if credentials aren't available it'll failover to another?

I've tried the following pattern but PQ still requires I provide valid credentials before I can refresh.

try
    let
        Source = Databricks.Catalogs(...)
    in
        Source

Otherwise 
    let
        Source = Excel.Workbook(...)
    in
        Source

[v-venuppu · Accepted solution]

Hi @kevinht ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Omid_Motamedise @PwerQueryKees for the prompt response.

You are on the right track with try...otherwise, but unfortunately Power Query still tries to validate all data sources at refresh time - even inside a try block  - so it will still prompt for Databricks credentials even if it ends up using the Excel fallback.

As a Workaround:

Use a parameter to switch between sources manually:

1.Create a text parameter (e.g., Environment) with values like "Prod" or "Dev".

2.Use conditional logic based on that parameter:

let
Source = if Environment = "Prod" then
Databricks.Catalogs(...)
else
Excel.Workbook(File.Contents("C:\path\to\your\local.xlsx"))
in
Source

This way, Power Query only tries to validate the data source that’s actually used - avoiding credential errors for the unused one.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[kevinht]

Thanks!  No idea why I never thought of using the basic control structures. But the extra insight about what gets validate at refresh time is great to add to my understanding.

[Omid_Motamedise]

try the following one

    let
        Source1 = Databricks.Catalogs(...),
        Source2 = Excel.Workbook(...), 
        
        Source= if Value.HasError(Source1) then Source2 else Source1
    in
        Source

[PwerQueryKees]

Not sure if this will work, but you could try something like this:

let
    Source = try 
                 Databricks.Catalogs(...) 
             Otherwise 
                 Excel.Workbook(...)
in
    Source