Forum Discussion
Datasource Selection based on PBIX refresh location
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
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
SourceThis 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.
4 Replies
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
SourceThis 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. 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.
Not sure if this will work, but you could try something like this:
let Source = try Databricks.Catalogs(...) Otherwise Excel.Workbook(...) in Sourcetry the following one
let Source1 = Databricks.Catalogs(...), Source2 = Excel.Workbook(...), Source= if Value.HasError(Source1) then Source2 else Source1 in Source
