Forum Discussion

JJ51's avatar
JJ51
Frequent Visitor
4 years ago

Dynamically Change Workspace Id/Name When Pulling Data Flow

Hello all!   My team is working on some BI embedded solutions for our customers and we are using a different workspace and dataflow based on the customer that is logged in (I know RLS could accompl...
Need Help
JJ51's avatar
JJ51
Frequent Visitor
4 years ago

Thanks for the prompt reply!

 

That's not exactly what I am looking for... I am trying to avoid any manual source changes if possible.  I had it set up using a parameter for the workspace name (as that's the only field that needs to change), but I am trying to avoid having to reconnect to different workspaces manually for every new customer.  

 

let
Source = PowerPlatform.Dataflows(null),
WorkspacesData = Source{[Id="Workspaces"]}[Data],
Workspace_Group = Table.SelectRows(WorkspacesData,each [workspaceName] >= "workspace1" and [workspaceName] < "workspace9999"),
Workspace_Name = Workspace_Group{0}[Data],
Dataflow_Name = Workspace_Name{[dataflowName="DF_Dimensional"]}[Data],
Entity_DM = Dataflow_Name{[entity="Entity_DM",version=""]}[Data]
in
Entity_DM

 

Above is the type of logic I am looking for, but I believe that it will always try to get the first workspace available to me, even if it is in a different workspace... 

FireFighter1017's avatar
FireFighter1017
Advocate III
2 years ago

I did just that using similar code.

Instead of drilling down to 

Workspace_Group{0}[Data]

You should expand the [Data] column.  This is how I do it now in Dataflows or elsewhere:

let
  Source = PowerPlatform.Dataflows([]),
  Navigation = Source{[Id = "Workspaces"]}[Data],
  #"Filtered rows" = Table.SelectRows(Navigation, each Text.StartsWith([workspaceName], MY_WORKSPACE_PREFIX)),
  #"Expanded Data" = Table.ExpandTableColumn(#"Filtered rows", "Data", {"dataflowName", "Data"}, {"dataflowName", "Data.1"}),
  #"Filtered rows 1" = Table.SelectRows(#"Expanded Data", each Text.StartsWith([dataflowName], MY_DATAFLOW_PREFIX)),
  #"Expanded Data.1" = Table.ExpandTableColumn(#"Filtered rows 1", "Data.1", {"entity", "Data"}, {"entity", "Data"}),
  #"Filtered rows 2" = Table.SelectRows(#"Expanded Data.1", each ([entity] = MY_ENTITY_NAME)),
  #"Removed other columns" = Table.SelectColumns(#"Filtered rows 2", {"Data"}),
  #"Expanded Data 1" = Table.ExpandTableColumn(#"Removed other columns", "Data", Table.ColumnNames(#"Removed other columns"{0}[Data]))
in
  #"Expanded Data 1"

 You just need to create 3 parameters:

- MY_WORKSPACE_PREFIX

- MY_DATAFLOW_PREFIX

- MY_ENTITY_NAME

 

I am currently reading multiple dataflows from 2 workspaces using this script and it works well I should say!

The only concern I may have is how Power BI will recognize the data connections if you try to apply this to multiple tennants.  I guess that as long a the script is running in a single tennant it shouldn't be a problem.