Variable source connection in DataFlow

[AlvinB]

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.

[BA_Pete · Accepted solution]

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]

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]

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

Pete

[AlvinB]

Hi, I ended up with the following code:

let
  SourceDev = Databricks.Catalogs("hostname1", "path1", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),
  SourceTest = Databricks.Catalogs("hostname2", "path2", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),
  SourceProd = Databricks.Catalogs("hostname3", "path3", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),

    // variable read from another DataFlow
  Environ = Configuration{0}[Environment],

  SelectedSource = if Environ = "DEV" then SourceDev else if Environ = "TEST" then SourceTest else SourceProd,

  Source2 = SelectedSource{[Name = Catalog, Kind = "Database"]}[Data],
  Source3 = Source2{[Name = "blur blur", Kind = "Schema"]}[Data],
  Source4 = Source3{[Name = "blur blur", Kind = "Table"]}[Data]
in
  Source4

Thanks for your idea on that. It is showing data but it's a burden to have 3 data source connections in every dataflows and even I need to change the hostname and path when there is a change in them.

I wanted the code like below:

let
    HostName = Table.First(Configuration)[HostName],
    HttpPath = Table.First(Configuration)[HttpPath],
    Source = Databricks.Catalogs(HostName, HttpPath, [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),
    #"Navigation 1" = Source{[Name = "blur blur", Kind = "Database"]}[Data],
    #"Navigation 2" = #"Navigation 1"{[Name = "blur blur", Kind = "Schema"]}[Data],
    #"Navigation 3" = #"Navigation 2"{[Name = "blur blur", Kind = "Table"]}[Data]
in
    #"Navigation 3"

 it dynamically connects the data source based on the value set in another Dataflow but in this case I could see below in "Manage Connections":

I mean a data source connection which has no connection details. Due to this when I refresh the dataflow I got below error:

Error: Credentials not found for data source '<Reducted data source>'.. Param1 = <Reducted data source> Request ID: blur blur...

[AlvinB]

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...

[AlvinB]

My bad... this was caused of the wrong httppath to DataBricks. I do not know why connection info has been changed. Anyway after putting the correct connection info, this error has gone. I'll be testing more and putting the result here.