Forum Discussion

AlvinB's avatar
AlvinB
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 t...
Need Help
  • BA_Pete's avatar
    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

AlvinB's avatar
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's avatar
BA_Pete
Icon for Super User rankSuper User
1 year ago

 

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

 

Pete

  • AlvinB's avatar
    AlvinB
    Frequent Visitor
    1 year ago

    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's avatar
      AlvinB
      Frequent Visitor
      1 year ago

      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.

  • AlvinB's avatar
    AlvinB
    Frequent Visitor
    1 year ago

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