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
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi JJ51 

 

You can use the Power Query Parameter. Create a query parameter for workspace id (or workspace name) and the other parameter for dataflow id (or dataflow name), then use parameter names to replace the static text names in your M code. 

 

Here is an example for SQL Server dynamic data sources: 

Change the Source of Power BI Datasets Dynamically Using Power Query Parameters 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

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.