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 to change parameters in each DataFlow. So I was trying to read and use the data source connection information from a separate DataFlow. Below is the code to read the connection information from a DataFlow in a DataFlow: ---------------------------let 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], <-- I could see data is loaded well here // HostName1= Table.First(Entity)[HostConnectionName], <-- When I use this , I got an error arelated with the credential HostName1= "blur blur blur", <--- When I use this, I got no error Source = Databricks.Catalogs(HostName1, "/sql/100/kkkhouse/somenumbers", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]), #"Navigation 1" = Source{[Name = "catalog_name", Kind = "Database"]}[Data], #"Navigation 2" = #"Navigation 1"{[Name = "schema_name", Kind = "Schema"]}[Data], #"Navigation 3" = #"Navigation 2"{[Name = "table_name", Kind = "Table"]}[Data]in #"Navigation 3"--------------------------- Actually, I am not that an expert on Power BI DataFlow and I do not stick above. I just want to get data in DataFlow from development or test environment based on the variables I defined somewhere. When the variables are changed, I just refresh DataFlows only. Any help or suggestion would be appreciated.

Frequent Visitor

1 year ago

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 to change parameters in each DataFlow. So I was trying to read and use the data source connection information from a separate DataFlow. Below is the code to read the connection information from a DataFlow in a DataFlow:

---------------------------

let

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], <-- I could see data is loaded well here

// HostName1= Table.First(Entity)[HostConnectionName], <-- When I use this , I got an error arelated with the credential

HostName1= "blur blur blur", <--- When I use this, I got no error

Source = Databricks.Catalogs(HostName1, "/sql/100/kkkhouse/somenumbers", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),

#"Navigation 1" = Source{[Name = "catalog_name", Kind = "Database"]}[Data],

#"Navigation 2" = #"Navigation 1"{[Name = "schema_name", Kind = "Schema"]}[Data],

#"Navigation 3" = #"Navigation 2"{[Name = "table_name", Kind = "Table"]}[Data]

#"Navigation 3"

---------------------------

Actually, I am not that an expert on Power BI DataFlow and I do not stick above. I just want to get data in DataFlow from development or test environment based on the variables I defined somewhere. When the variables are changed, I just refresh DataFlows only. Any help or suggestion would be appreciated.

Need Help

BA_Pete

1 year ago

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
  SelectedSource

Pete

6 Replies

BA_Pete

Super User

1 year ago

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
  SelectedSource

Pete

AlvinB

Frequent Visitor

1 year ago

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
  SelectedSource

Originally 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
  Source4

Anyway 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
Super User
1 year ago

Did the top one work and, if not, what failed e.g. error codes etc.?

Pete

Forum Discussion

Variable source connection in DataFlow

6 Replies

Related content

connection to dataflows

dataflow connection

Dataflow Gen2: Variable Library references fail in Connection Settings

Dataflow Gen2 Connections

Dataflow gen2 using variables from Variable Libraries Failing