Dynamically Change Workspace Id/Name When Pulling Data Flow

[JJ51]

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 accomplish this, but for reasons outside my control we went a different way).  We are trying to make new client setup as seamless as possible. On the BI service, embedded, and data source side of things it is VERY dynamic.  However on the Desktop side, I am hoping to find a way to dynamically grab the Workpace name so I can get the data flow from a specific workspace.  

The report, as well as the data flow name and entity will be static.  Below is an example of what I'm currently doing:

let
            Source = PowerPlatform.Dataflows(null),
            Workspaces = Source{[Id="Workspaces"]}[Data],
            Workspace_Name = Workspaces{[workspaceName="workspace1"]}[Data],
            Dataflow_Name = Workspace_Name{[dataflowName="Dimensional_DF"]}[Data],
            Entity_Name = Dataflow_Name{[entity="Customer_DM",version=""]}[Data]
        in 
            Entity_Name

Any ideas or help would be appreciated!

JJ

[JamesCameronMat]

Not sure if this is resolved, or if my response will help. But I have created a template pbix file on my GitHub account:
https://github.com/JamesCameronMathews/PowerBI_Stuff/blob/main/PBI_Dataflow_Template.pbix 

This approach allows parametric management of the selected datasource.

-Paramaters (query group):

-- workspaceName (parameter): Parameter controlling the workspace containing the target dataflow

-- dataflowName (parameter): Parameter controlling the target dataflow

-- Workspace (list): List of available workspaces (auto-populated)

-- Dataflow_Name (list): List of available dataflows (auto-populated)

-- DataflowSelector (table): Table with the id for the target workspace and dataflow, dependant on parameters selected

- YourTable (Table): Example query showing how to use target a table in your dataflow using your above

[FireFighter1017]

I am reusing the same dataflows in multiple workspaces as well and I'm also looking for a way to dynamically reference the dataflows without having to constantly change al the parameters.

Being able to change only the workspace and have all the dataflows pointing at that workspace would be a start.

I am currently using parameters because I also need to dynamically access those dataflows from other dataflows and it is the ony way Power BI Service would allow me to do it.

The use of parameters reduce the amount of changes I have to do in queries.

But editing the queries is necessary.  You could, I guess create a parameter that would be bound to the workspace name and dataflows but you would still need to edit the query in order to update this parameter.  ... I think.

I haven't tried this yet, but here's a link to how dynamic parameters works: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

*edit* something I just tried with Excel and it worked!:

Create a parameter "WS_PARAMETER" with the workspace name you want to use a default and set it up as a list of values with all the workspaces you want to be able to use.

Create a query named "Dataflows" with this code:

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id="Workspaces"]}[Data],
    #"Added Custom" = Table.AddColumn(Workspaces, "Dataflows", each [Data]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([workspaceName] = WS_PARAMETER)),
    #"Expanded Dataflows" = Table.ExpandTableColumn(#"Filtered Rows", "Dataflows", {"dataflowId", "dataflowName", "Data"}, {"Dataflows.dataflowId", "Dataflows.dataflowName", "Dataflows.Data"})
in
    #"Expanded Dataflows"

This table will list all the dataflows available in the workspace you selected, including the "Data" column that contains the entities.

Create another query named after the dataflow you want to use, "DF1" for this example.  This query will reference the "Dataflows" query you just created.  You can replace the "DF1" value in the #"Filtered Dataflow" instruction to use your dataflow name :

let
    Source = Dataflows,
    #"Filtered Dataflow" = Table.SelectRows(Source, each ([Dataflows.dataflowName]= "DF1")),
    entities = #"Filtered Dataflow"{0}[Dataflows.Data]
in
    entities

Then you can reference "DF1" to select the table you want.

I guess if you bind this parameter in your report with the field in the table "Dataflows", it would allow report users to select the source they want to use.

But I only tested it in Excel so far.  I can edit after I have tried it using a PowerBI Report.

[v-jingzhang]

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]

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]

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.