Forum Discussion
Solved
Variable source connection in DataFlow
Hi all, I want to achieve to load tables from variable source like below. I set the parameters for source connection and was using those to load tables but I got many DataFlows and it's a burdent t...
Hi AlvinB ,
I'm not sure I fully understand your requirements to be honest, but here's what I think you're aiming for:
let // Below is your dataflow for controlling whether you want DEV or PROD data in your query // I assume this just a single value, 1 or 0, to flag whether you want PROD or DEV source // It can be set up as follows: // let // Source = 0, // or 1 for PROD // convToTable = Table.FromValue(Source) // in // convToTable Param_dataflow = PowerPlatform.Dataflows(), Param_Workspaces = Source_dataflow{[Id = "Workspaces"]}[Data], Param_Workspace = Workspaces{[workspaceId = WorkspaceID]}[Data], Param_Dataflow = Workspace{[dataflowId = DataflowID]}[Data], Param_Value = Dataflow{[entity = "Parameter_On_off"]}[Data], // End get parameter value // Get DEV source DEV_dataflow = PowerPlatform.Dataflows(), DEV_Workspaces = Source_dataflow{[Id = "Workspaces"]}[Data], DEV_Workspace = Workspaces{[workspaceId = WorkspaceID]}[Data], DEV_Dataflow = Workspace{[dataflowId = DataflowID]}[Data], DEV_Table = Dataflow{[entity = "Configuration"]}[Data], // End get DEV source // Get PROD source PROD_Source = Databricks.Catalogs(HostName1, "/sql/100/kkkhouse/somenumbers", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]), PROD_Database = Source{[Name = "catalog_name", Kind = "Database"]}[Data], PROD_Schema = PROD_Database{[Name = "schema_name", Kind = "Schema"]}[Data], PROD_Table = PROD_Schema{[Name = "table_name", Kind = "Table"]}[Data], // End get PROD source // Select DEV or PROD source SelectedSource = if Param_Value[Value]{0} = 0 then DEV_Table else PROD_Table in SelectedSourcePete
BA_Pete
Super User
Super UserHi AlvinB ,
I'm not sure I fully understand your requirements to be honest, but here's what I think you're aiming for:
let
// Below is your dataflow for controlling whether you want DEV or PROD data in your query
// I assume this just a single value, 1 or 0, to flag whether you want PROD or DEV source
// It can be set up as follows:
// let
// Source = 0, // or 1 for PROD
// convToTable = Table.FromValue(Source)
// in
// convToTable
Param_dataflow = PowerPlatform.Dataflows(),
Param_Workspaces = Source_dataflow{[Id = "Workspaces"]}[Data],
Param_Workspace = Workspaces{[workspaceId = WorkspaceID]}[Data],
Param_Dataflow = Workspace{[dataflowId = DataflowID]}[Data],
Param_Value = Dataflow{[entity = "Parameter_On_off"]}[Data],
// End get parameter value
// Get DEV source
DEV_dataflow = PowerPlatform.Dataflows(),
DEV_Workspaces = Source_dataflow{[Id = "Workspaces"]}[Data],
DEV_Workspace = Workspaces{[workspaceId = WorkspaceID]}[Data],
DEV_Dataflow = Workspace{[dataflowId = DataflowID]}[Data],
DEV_Table = Dataflow{[entity = "Configuration"]}[Data],
// End get DEV source
// Get PROD source
PROD_Source = Databricks.Catalogs(HostName1, "/sql/100/kkkhouse/somenumbers", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),
PROD_Database = Source{[Name = "catalog_name", Kind = "Database"]}[Data],
PROD_Schema = PROD_Database{[Name = "schema_name", Kind = "Schema"]}[Data],
PROD_Table = PROD_Schema{[Name = "table_name", Kind = "Table"]}[Data],
// End get PROD source
// Select DEV or PROD source
SelectedSource = if Param_Value[Value]{0} = 0 then DEV_Table else PROD_Table
in
SelectedSource
Pete
Thanks for your help. Yes almost same as I intended. I tried below:
let // Get the connection information from a DataFlow Source_dataflow = PowerPlatform.Dataflows(), Workspaces = Source_dataflow{[Id = "Workspaces"]}[Data], Workspace = Workspaces{[workspaceId = WorkspaceID]}[Data], Dataflow = Workspace{[dataflowId = DataflowID]}[Data], Entity = Dataflow{[entity = "Configuration"]}[Data], // Get the host name & Http path to connect HostName = Entity{0}[HostName], HttpPath = Entity{0}[HttpPath], SourceDev1 = Databricks.Catalogs("Dev host", "Dev http", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]), SourceDev2 = SourceDev1{[Name = "dev catalog", Kind = "Database"]}[Data], SourceDev3 = SourceDev2{[Name = "dev database", Kind = "Schema"]}[Data], SourceDev4 = SourceDev3{[Name = "dev table", Kind = "Table"]}[Data], SourceTest1 = Databricks.Catalogs("Test host", "Test http", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]), SourceTest2 = SourceTest1{[Name = "Test catalog", Kind = "Database"]}[Data], SourceTest3 = SourceTest2{[Name = "Test database", Kind = "Schema"]}[Data], SourceTest4 = SourceTest3{[Name = "Test table", Kind = "Table"]}[Data], SelectedSource = if HostName = "DEV" then SourceDev4 else SourceTest4 in SelectedSourceOriginally I was intending:
let // Get the connection information from a DataFlow Source_dataflow = PowerPlatform.Dataflows(), Workspaces = Source_dataflow{[Id = "Workspaces"]}[Data], Workspace = Workspaces{[workspaceId = WorkspaceID]}[Data], Dataflow = Workspace{[dataflowId = DataflowID]}[Data], Entity = Dataflow{[entity = "Configuration"]}[Data], // Get the host name & Http path to connect HostName = Entity{0}[HostName], HttpPath = Entity{0}[HttpPath], Source1 = Databricks.Catalogs(HostName , HttpPath , [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]), Source2 = Source1 {[Name = "dev catalog", Kind = "Database"]}[Data], Source3 = Source2 {[Name = "dev database", Kind = "Schema"]}[Data], Source4 = Source3 {[Name = "dev table", Kind = "Table"]}[Data], in Source4Anyway I could see data in DataFlow but after saving and returning to Power BI Workspace, when I try to refresh the DataFlow manually, I got this error:
Error: Credentials not found for data source '<Reducted data source>'.. Param1 = <Reducted data source> Request ID: blur blur..
I provided the correct credentials here:
I'm using 'Oauth2' in the Authentication method and set 'None' or 'Organisational' but both are not working. I got the same error as above.
Thanks.
- BA_Pete
Did the top one work and, if not, what failed e.g. error codes etc.?
Pete
How strange it is.. Yesterday it was working in DataFlow buit not working in refreshing. But now it is showing below in DataFlow:
404 Endpoint error. I've put the correct credential in "Manage Connections" for both of Dev & Test. How hard to flexibly connect to the data source...
