Refresh Power BI Datasets via its name instead of Id

Case
In our last post we refreshed a Power BI dataset from within an ADF or Synapse pipeline. For this we used the IDs of the PBI Workspace and Dataset, but it would be much user friendly to use the name instead of the ID.

Translate Name to Id

Solution
This is a follow up post. Make sure you first create a Pipeline that either uses a Service Principal (SP) or the Managed Identity (MSI) to authorize against Power BI. This example adds three aditional activities before the Web Activity called Call dataset refresh. If you used the Service Principal it is between Get AAD Token and Call dataset refresh. 

1) Change Parameters

In the previous post we used the parameters named WorkspaceId and DatasetId. Let's change those to WorkspaceName and DatasetName. The type is still a string for both parameters.

Change parameters from Id to Name

2) Get WorkspaceId

To translate the Workspace Name to a Workspace Id we will call a Rest API with a Web Activity. Make sure to use the same authorization as you used in the Call dataset refresh.

• Add Web Activity to your existing pipeline
• Give it the name Get WorkspaceId. It will be used later on.
• Set the URL to the following expression
  @concat(
  'https://api.powerbi.com/v1.0/myorg/groups?$filter=name%20eq%20''',
  pipeline().parameters.WorkspaceName,'''')
  It uses the parameter from step 1 in the filter option from the Rest API. Note that the space is translated to %20
• Set Method to GET
• Now either add a authorization header for SP or set the Authenication for the MSI

This will return 1 workspace (if it exists).

Get Workspace Id via its Name

3) Get All Datasets

Next step is to translate the Dataset Name to and ID. This is also done via a Rest API, but unfortunately it doesn't support a filter. So we will get ALL datasets from the Workspace and then filter it afterwards. Again make sure to use the same authorization as you used in the Call dataset refresh.

• Add Web Activity to your existing pipeline and connect it to Get WorkspaceId
• Give it the name Get All Datasets. It will be used later on.
• Set the URL to the following expression
  @concat('https://api.powerbi.com/v1.0/myorg/groups/',
  activity('Get WorkspaceId').output.value[0].id,
  '/datasets')
  It will use the parameter from step to get all datasets from a specific workspace.
• Set Method to GET
• Now either add a authorization header for SP or set the Authenication for the MSI

This will return all datasets from the workspace from step 2 (if you have access).

Get all datasets from a workspace

4) Filter DatasetName

The last new activity is a Filter activity which we will use to filter all the datasets from step 3 to only the one we need.

• Add Filter Activity to your existing pipeline and connect it to Get All Datasets
• Give it the name Filter DatasetName. It will be used later on.
• Set Items expression to
  @activity('Get All Datasets').output.value
  This is the output from step 3
• Set Condition expression to
  @equals(item().name,pipeline().parameters.DatasetName)
  This is the second parameter from step 1

Filter all datasets with a dataset name to get only 1

5) Change expressions

Now go to all the successive (Web) activities and change the expressions where you used a parameter. Now you need the get the WorkspaceId with:
activity('Get WorkspaceId').output.value[0].id

and the DatasetId with:
activity('Filter DatasetName').output.value[0].id

Conclusion

In this follow up post you learned how to translate the Workspace Name and Dataset Name to an ID which is required for the Rest API calls. With the three extra activities you just made your pipeline a little more friendlier to use, but also a little more monkey proof. Because if you delete a dataset and publish it again, it gets a different ID and then you also need to change your pipeline parameter. In a next follow up post we will refresh only certain tables within a Power BI dataset.

thx to colleague Hans Timmerman for helping.