Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
---------------------------
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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.
Did the top one work and, if not, what failed e.g. error codes etc.?
Pete
Proud to be a Datanaut!
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...
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...
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.