Refresh single table in PBI Dataset from within ETL

Case
I have one big Power BI dataset with multiple source tables and one of those source tables is refreshed once every hour and all the other tables are only refreshed once a day. How can refresh only specific tables within my Power BI dataset via an ADF or Synapse pipeline?

Using XMLA enpoints in Power BI

Solution
To accomplish this you need the XMLA enpoints in Power BI. Those XMLA enpoints provide access to the Analysis Services engine in Power BI giving you extra options for your dataset like only refreshing a single table (or a couple of tables).

However, XMLA enpoints are only availble for premium workspaces. So you either need Power BI premium (capacity) or Power BI premium per user. This doesn't work for Power BI PRO.

1) Enable XMLA endpoints

First make sure the XMLA endpoints are enabled in the Tenant settings. For this you need a Power BI administrator role.

• Log in to PowerBI.com
• Click on the gear icon in the top right corner
• Choose Admin Portal
• Click on Tenant settings in the Admin portal menu. If you don't see that option then you don't have the PBI admin role
• Scrole down to Integration settings
• Expand 'Allow XMLA endpoints and Analyze in Excel with on-premises datasets'
• Check whether it is enabled for either The entire organization or Specific security groups. If it is set to Specific security groups make sure that your user and the Service Principal or the Managed Identity of ADF/Synapse is in that group.

Allow XMLA endpoints and
Analyze in Excel with on-premises datasets

2) Set to Read-Write

Depending or the premium license type you go to Premium Per User or to Capacity settings in the same Admin portal menu. And if you have multiple Capacities then first go to the specific Capacity.

• In the Admin portal go to Premium Per User or to Capacity settings (depending on your license)
• Locate the XMLA Endpoint setting and set it to Read Write

XMLA endpoints for Premium Capacity

XMLA endpoints for Permium per user

3) Premium workspace

Now go to the Power BI Workspace where your dataset is located and make sure the workspace is a Premium workspace. You can change it in the Settings in the upper right corner.

4) Create JSON message 

Now go to your ADF or Synapse pipeline that refreshes the Power BI dataset where you either use a Service Principal or a Managed Identity to access Power BI. All those steps are described in details in those two posts.

Web Activity to refresh a Power BI dataset

The body of this Web Activity contains options for refreshing the dataset. Our basic example only contained an option to skip notifications: {"notifyOption":"NoNotification"}. This message can also contain settings to refresh only certain tables. Check DatasetRefreshObjects and DatasetRefreshType in the documentation and create a JSON messages like this where we refresh only the data of two tables: myFact and myDim.

{"notifyOption":"NoNotification"
,"objects":"[{"table":"myFact"},{"table":"myDim"}]"
,"type":"DataOnly"}

Refresh specific tables in a PBI dataset

5) Create JSON message via SQL

If you want to make that a little more flexible then you can put a Lookup activity in front of it that gets the tables from query like this:

SELECT CONVERT(VARCHAR(MAX),
    (SELECT [table]
    FROM [META].[DatasetContinuousRefresh]
	WHERE Active = 1
    FOR JSON PATH)
) as body

Lookup activity to get table names in a JSON output

Instead of the hardcoded body in the Web activity you can create something more dynamic with an expression like:

@concat('{"notifyOption":"NoNotification"'
,if(pipeline().parameters.ContinuousLoad,concat(',"objects":"',activity('Create body call dataset refresh').output.firstRow.body,'"'),'')
,',"type":"DataOnly"}')

The entire pipeline to refresh a Power BI dataset via its name (instead of the ID) looks something like this:

The complete refresh PBI dataset pipeline

Please check the previous blog posts for all the details

1a) Refresh Power BI Datasets with ADF or Synapse (SP)
1b) Refresh Power BI Datasets with ADF or Synapse (MSI)
2) Refresh Power BI Datasets via its name instead of Id

Conclusions

In this post we showed you how to refresh only a single table or just a couple of tables within a Power BI dataset. This could save you a lot of refresh time if you don't want to refresh all tables each and every time. The only downside is that Power BI PRO is not enough and you need access to the Power BI admin portal to set the XMLA settings.

thx to colleague Hans Timmerman for helping.

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.

Refresh Power BI Datasets with ADF or Synapse (MSI)

Case
I want to refresh my Power BI Dataset as soon as my ETL process has finished. I don't want to schedule my dataset refresh x minutes/hours after my ADF(/Synapse) pipeline schedule because then I'm either refreshing too soon or waiting unnecessarily long to start the refresh. How can I minimize the time between the end of my ETL proces and the start of my dataset refresh?

Refresh Power BI Dataset

Solution
In the previous blog post we showed you how to use the Rest APIs from Power BI to refresh a dataset from within your ADF(/Synapse) pipeline. For that example we used an Azure Active Directory Service Principal (SP), but there is also an option to use the Managed (Service) Identity (MSI) from ADF(/Synapse). 

Using the MSI not only saves you creating a SP (and storing/recycling its secret), but the pipeline also uses less activities which makes it more readable. However you probably still need some help from a Domain Administrator and/or an Office 365 Administrator, because:

• You need to create an Azure Active Directory Group and add the MSI of your ADF or Synapse to it.
•  After that you need to use that AAD Group within the Tenant Settings of Power BI.

The basic version of the pipeline looks like this. The more complex version could for example retrieve the required workspace id and dataset id by its name via additional Rest API calls to Power BI. More on that in the conclusions.

Basic pipeline to refresh a PBI dataset

Now below all the steps to create this pipeline that can refresh your Power BI datasets. There are a lot of steps, but don't let that scare you. It seems way more difficult then it really is.

1) Finding the MSI of your ADF/Synapse workspace

For this example we will give the Managed (Service) Identity of your ADF or Synapse workspace access to Power BI, but how can you find this MSI? They can be found in the Azure Active Directory (AAD) as an EnterPrise Application. This means you can authorize it for services like Power BI just like a regular user from the AAD. The EnterPrise Application has a name (equals to the name of your ADF or Synapse workspace), an object id (a guid) and an application id (also a guid).

Find your ADF (or Synapse workspace) in the AAD

The Name is the one you need and the Application ID is the one you can use to double check that you have the correct item from the AAD. ADF also shows the Application ID on its Properties page as Managed Identity Application ID. However Synapse only shows the Managed Identity object ID which is useless in this case.

2) Allow MSI to use Power BI APIs

For this first real step you need access to the Power BI admin portal to give the MSI of ADF(/Synapse) access to the Rest APIs from Power BI. 

• Log in to Power BI online and click on the gear icon in the upper right corner
• Click on Admin portal in the menu to open the portal
• Then click on Tenant settings (if you don't see this, continue after the next image)
• Scroll down to the Developer settings and locate 'Allow service principal to use Power BI APIs'

Either allow the entire organization to use the Rest APIs (not recommended) or specify an Azure Active Directory Group and make sure your MSI is a member of that group (this is where you probably need some help from the Domain Administrator. 

PBI Admin portal - Tenant Settings

If you couldn't find the tenant settings in the Admin Portal from Power BI then you need an Power BI administrator role. Your Office 365 administrator can either give you the role or do the steps above for you. If you are able get this role then you should probably consider using Azure AD Privileged Identity Management (PIM) to first get approval from a colleage to temporarily get this role and also use the PBI audit log to record all logins to the admin portal and record all changes in the tenant settings. 

Not a PBI admin

3) Give permissions in Power BI Workspace

Next step is to give your MSI contributor rights in the PBI Workspace that contains the dataset that you want to refresh.

• Go to your Power BI workspace and click on Access in the upper right corner
• Search for your Service Principal by its name (ours is called PowerBI)
• Select the right permission (contributor) and click on the Add button
• Verify the changed list of permissions and then click on the Close button

Give MSI permissions in your workspace

4) Refresh Dataset

Now go to your pipeline in ADF (or Synapse). For refreshing a dataset we need both the workspace and the dataset ID from Power BI. We will store these IDs in two Pipeline parameter so that you can use the pipeline to refresh different datasets. In a later post we will show how to use the names instead of the IDs.

Pipeline parameters for Workspace ID and Dataset ID

The refresh command will be given via a Web activity that executes the Refresh Dataset In Group Rest API from Power BI. The refresh call is asynchronous, so it does not wait for the refresh to finish.

• Add a Web Activity and give it useful name.
• The URL uses the two IDs stored in the two pipeline parameters above. Use an expression like this to build the URL (check the parameters names):
  @concat(
      'https://api.powerbi.com/v1.0/myorg/groups/'
      , pipeline().parameters.WorkspaceGuid
      , '/datasets/'
      , pipeline().parameters.DatasetGuid
      , '/refreshes'
      )
• Set Method to Post
• The Body can be used to set certain options like no notifications. See the documentation for more options. Enter the following JSON in the body:
  {"notifyOption":"NoNotification"}
• Set Authentication to System Assigned Managed Identity
• Set Resource to https://analysis.windows.net/powerbi/api

Refresh a Power BI dataset via Rest API

5) Check refresh status

The last steps are optionally, but if you want to be sure your dataset has been successfully refreshed we need to check the dataset refresh status in a loop. For this task the UNTIL loop is the best option. Within the loop we have a Web Activity to check the status and an Wait activity (within an If condition) to wait a couple of minutes/seconds because you don't want to check the status each second.

The check status construction

A slightly simpler option would be to just put a Wait Activity before the Web Activity instead of the If construction after the Web Activity.

Now all the individual steps and their screentshot:

• Add an Until and give it a suitable name
• The Expression continues the loop until the status is not equals 'Unknown' :
  @not(
      equals(
          first(
              json(
                  string(
                      activity('Get dataset refresh status').output
                  )
              ).value
          ).status,'Unknown'
      )
  )
  Note the name of the activity that will be inside the Until loop (if it is not yet there, it will give an error)
• Consider changing the default 7 day timeout to the expected max refresh duration with an extra couple of minutes to be sure

Until loop

• Add a Web Activity within the Until loop and name it 'Get dataset refresh status'. If you change that name, then you have to change it in all the expressions from the next steps.
• The URL is simular to the Refresh dataset, but it uses a different action 'refreshes' and a top 1 to only get the latest status:
  @concat(
      'https://api.powerbi.com/v1.0/myorg/groups/'
      ,pipeline().parameters.WorkspaceGuid
      ,'/datasets/',pipeline().parameters.DatasetGuid
      ,'/refreshes?$top=1'
      )
• Set Method to Get
• Add a header with the name 'Content-Type' and the value 'application/json'
• Set Authentication to System Assigned Managed Identity
• Set Resource to https://analysis.windows.net/powerbi/api

Web Activity to get the refresh status

• Add an If Condition after the Web activity
• The expression checks whether the Web Activity still returns 'Unknown' as status (same expression as the Until loop):
  @equals(
      first(
          json(
              string(
                  activity('Get dataset refresh status').output
              )
          ).value
      ).status,'Unknown'
  )

Check status value with IF

• Last step is to add a Wait Activity in the True condition.
• Choose a smart number of seconds. Too low and you will get a lot of unnecessary checks. Too high and you will get an unnecessary long turnaround time.

Wait X seconds

6) Fail pipeline if refresh failed

If the dataset refresh in Power BI fails then the Web Activity will show you the status, but it won't fail the pipeline. The Until loop ends when the status changes. So by adding an additional If Condition that checks whether the refresh status is failed, allows you to use the Fail activity to fail the pipeline.

• Add a If Condition after the Until loop
• The expression is simulair to the If Condition within the loop. Just a different status:
  @equals(
      first(
          json(
              string(
                  activity('Get dataset refresh status').output
              )
          ).value
      ).status,'Failed'
  )
  
  
• Within the True condition add the Fail activity
• You can either enter a default fail message or try to get the error from the Web Activity with an expression like this:
  @string(
      json(
          first(
              json(
                  string(
                      activity('Get dataset refresh status').output
                  )
              ).value
          ).serviceExceptionJson
      )
  )
• Last task is to pick an error code. This will show up in the logs.

Fail pipeline if refresh fails

Conclusion

In this post you learned how to refresh a Power BI dataset from within your ETL pipelines. Hopefully you weren't scared of due all those steps. In a next blog post we will show you how to use the workspace name and data set name instead of their IDs because that is much more user friendly and even a little safer because when you delete a dataset and publish it again it will get a different ID. This will need three additional tasks for the translation part.

Translate names to ID's

Another extension that we will show in yet an other blog post is how to refresh only certain tables within that dataset. This will cause some changes to the Body of the refresh call, but also an additional Tenant setting change and... Power BI Premium.

thx to Simon Zeinstra for suggesting the simpler version en helping out with the resource url

Refresh Power BI Datasets with ADF or Synapse (SP)

Case
I want to refresh my Power BI Dataset as soon as my ETL process has finished. I don't want to schedule my dataset refresh x minutes/hours after my ADF(/Synapse) pipeline schedule because then I'm either refreshing too soon or waiting unnecessarily long to start the refresh. How can I minimize the time between the end of my ETL proces and the start of my dataset refresh?

Refresh Power BI Dataset

Solution
You can use the Rest APIs from Power BI to refresh a dataset from within your ADF(/Synapse) pipeline. However for this solution you (the DWH / BI developer) will probably need some help from a Domain Administrator and/or an Office 365 Administrator, because:

• You need an Azure Active Directory Service Principal (SP) and know its name, id and secret
• This SP needs to be in an Azure Active Directory Group that will used within the Tenant Settings of Power BI.

For this solution we will store the secret (and optionally the id) of the SP in an Azure Key Vault. Then retrieve it within the ADF(/Synapse) pipeline and use it to retrieve a token from the Active Direcory. The last step is to use that token to login to Power BI and refreh the dataset.

Use secret to create token and login to Power BI

The basic version of the pipeline looks like this. The more complex version could for example retrieve the required workspace id and dataset id by its name via additional Rest API calls to Power BI. More on that in the conclusions.

Basic version of pipeline

Now below all the steps to create this pipeline that can refresh your Power BI datasets. There are a lot of steps, but don't let that scare you. It seems way more difficult then it really is.

1) Allow service principal to use Power BI APIs

For this first step you need access to the Power BI admin portal to give the Service Principal access to the Rest APIs from Power BI. 

• Log in to Power BI online and click on the gear icon in the upper right corner
• Click on Admin portal in the menu to open the portal
• Then click on Tenant settings (if you don't see this, continue after the next image)
• Scroll down to the Developer settings and locate 'Allow service principal to use Power BI APIs'

Either allow the entire organization to use the Rest APIs (not recommended) or specify an Azure Active Directory Group and make sure your Service Principal is a member of that group (this is where you probably need some help from the Domain Administrator. 

PBI Admin portal - Tenant Settings

If you couldn't find the tenant settings in the Admin Portal from Power BI then you need an Power BI administrator role. Your Office 365 administrator can either give you the role or do the steps above for you. If you are able get this role then you should probably use Azure AD Privileged Identity Management (PIM) to first get approval from a colleage to temporarily get this role and also use the PBI audit log to record all logins to the admin portal and record all changes in the tenant settings. 

Not a PBI admin

2) Give permissions in Power BI Workspace

Next step is to give your Service Principal contributor rights in the PBI Workspace that contains the dataset that you want to refresh.

• Go to your Power BI workspace and click on Access in the upper right corner
• Search for your Service Principal by its name (ours is called PowerBI)
• Select the right permission (contributor) and click on the Add button
• Verify the changed list of permissions and then click on the Close button

Give SP permissions in your workspace

3) Add secret to Azure Key Vault

To create the AAD Token in the next step we need:

• the ID (a guid) of the SP
• the secret of the SP
• the AAD tenant ID

You could store them in parameters or hardcode them in your pipeline, but we will store them as secrets in an Azure Key Vault. If you already used an Azure Key Vault in ADF(/Synapse) before then you know you have to give the Managed Service Identity of your ADF(/Synapse) 'Get' and 'List' access to read the secrets (see step 3 in this blog post).

The ID and secret of the SP are provided by the person who created the SP in the Azure Active Directory. The tenant ID can also be found in AAD, but you can also retrieve it from the about box in Power BI online. Store these three items as a secret in the Key Vault.

The guid in the Tenant URL is the Tenant ID

To retrieve the AAD token in the next step we will use a Web Activity. Unfortunately the Web activity doesn't work with the Key Vault Linked Service. Therefor we need to use Web Activities to retrieve all secrets from the Key Vault. In a previous blog post we showed you how to do this in detail. Below a summary of all those steps (which you must be repeated for all three secrets).

• Add a Web Activity and give it useful name, because it will be used in the next activity.
• Use the Secure output checkbox to prevent secrets showing up in the log
• Set the URL to https://{Name Keyvault}.vault.azure.net/secrets/{Name Secret}?api-version=7.0 and replace the values between {} with the real value. Tip: create a Global Parameter for the Key Vault name and use that to build the URL with an expression like this:
  @concat(
      'https://'
      , pipeline().parameters.KeyVaultName
      , '.vault.azure.net/secrets/sp_secret/?api-version=7.1'
      )
• Set Method to Get
• Under Advanced select Managed Identity as Authentication
• And set Resource to https://vault.azure.net

Web Activity to retrieve secrets from the Key Vault

4) Retrieve AAD token

Next step is to use yet another Web Activity to retrieve an AAD token by providing the output from the previous three Web Activities.

• Add a Web Activity and give it useful name, because its name will be used in the next activity.
• Use the Secure input and output checkbox to prevent secrets or tokens showing up in the log
• The URL uses the Tenant ID from the previous step. Use an expression like this to build the URL (check the activity name):
  @concat(
      'https://login.microsoftonline.com/'
      , activity('Get TenantId from AKV').output.value
      , '/oauth2/v2.0/token'
      )
• Set Method to Post
• Add a header with the name 'Content-Type' and the value 'application/x-www-form-urlencoded'
• The body is also an expression that uses the secrets of the previous step (check the names):
  @concat(
      'client_id='
      , activity('Get ClientId from AKV').output.value
      , '&scope=https://analysis.windows.net/powerbi/api/.default'
      , '&client_secret='
      , activity('Get Secret from AKV').output.value
      , &grant_type=client_credentials'
      )

Get AAD Token

5) Refresh Dataset

For refreshing a dataset we need the Token from the previous step, but also the ID of both the workspace and the dataset. We will store these IDs in two Pipeline parameter so that you can use the pipeline to refresh different datasets. In a later post we will show how to use the names instead of the IDs.

Pipeline parameters for Workspace ID and Dataset ID

The refresh command is again an other Web activity that executes the Refresh Dataset In Group Rest API from Power BI. The refresh call is asynchronous, so it does not wait for the refresh to finish.

• Add a Web Activity and give it useful name.
• Use the Secure input checkbox to prevent tokens showing up in the log
• The URL uses the two IDs stored in the two pipeline parameters above. Use an expression like this to build the URL (check the parameters names):
  @concat(
      'https://api.powerbi.com/v1.0/myorg/groups/'
      , pipeline().parameters.WorkspaceGuid
      , '/datasets/'
      , pipeline().parameters.DatasetGuid
      , '/refreshes'
      )
• Set Method to Post
• Add a header with the name 'Authorization' and the value is an expression with the Token of the previous step:
  @concat(
      string(activity('Get AAD Token').output.token_type)
      , ' '
      , string(activity('Get AAD Token').output.access_token)
      )
• The Body can be used to set certain options like no notifications. See the documentation for more options. Enter the following JSON in the body:
  {"notifyOption":"NoNotification"}

Refresh a Power BI dataset via Rest API

6) Check refresh status

The last steps are optionally, but if you want to be sure your dataset has been successfully refreshed we need to check the dataset refresh status in a loop. For this task the UNTIL loop is the best option. Within the loop we have a Web Activity to check the status and an Wait activity (within an If condition) to wait a couple of minutes/seconds because you don't want to check the status each second.

The check status construction

A slightly simpler option would be to just put a Wait Activity before the Web Activity instead of the If construction after the Web Activity.

Note: If you expect that the refresh takes more than an hour then you have some additional work to do, because the lifetime of the token is 60 to 90 minutes. In that case you need to copy the Get AAD Token to the loop and perhaps add an extra If Condition to only get a new token if it is about to expire.

Now all the individual steps and their screentshot:

• Add an Until and give it a suitable name
• The Expression continues the loop until the status is not equals 'Unknown' :
  @not(
      equals(
          first(
              json(
                  string(
                      activity('Get dataset refresh status').output
                  )
              ).value
          ).status,'Unknown'
      )
  )
  Note the name of the activity that will be inside the Until loop (if it is not yet there, it will give an error)
• Consider changing the default 7 day timeout to the expected max refresh duration with an extra couple of minutes to be sure

Until loop

• Add a Web Activity within the Until loop and name it 'Get dataset refresh status'. If you change that name, then you have to change it in all the expressions from steps 6 en 7.
  
• Use the Secure input checkbox to prevent tokens showing up in the log
• The URL is simular to the Refresh dataset, but it uses a different action 'refreshes' and a top 1 to only get the latest status:
  @concat(
      'https://api.powerbi.com/v1.0/myorg/groups/'
      ,pipeline().parameters.WorkspaceGuid
      ,'/datasets/',pipeline().parameters.DatasetGuid
      ,'/refreshes?$top=1'
      )
• Set Method to Get
• Add a first header with the name 'Authorization' and the value is an expression with the Token of the previous step:
  @concat(
      string(activity('Get AAD Token').output.token_type)
      , ' '
      , string(activity('Get AAD Token').output.access_token)
      )
• Add a second header with the name 'Content-Type' and the value 'application/json'

Web Activity to get refresh status

• Add an If Condition after the Web activity
• The expression checks whether the Web Activity still returns 'Unknown' as status (same expression as the Until loop):
  @equals(
      first(
          json(
              string(
                  activity('Get dataset refresh status').output
              )
          ).value
      ).status,'Unknown'
  )

• Last step is to add a Wait Activity in the True condition.
• Choose a smart number of seconds. Too low and you will get a lot of unnecessary checks. Too high and you will get an unnecessary long turnaround time.

Wait X seconds

7) Fail pipeline if refresh failed

If the dataset refresh in Power BI fails then the Web Activity will show you the status, but it won't fail the pipeline. The Until loop ends when the status changes. So by adding an additional If Condition that checks whether the refresh status is failed, allows you to use the Fail activity to fail the pipeline.

• Add a If Condition after the Until loop
• The expression is simulair to the If Condition within the loop. Just a different status:
  @equals(
      first(
          json(
              string(
                  activity('Get dataset refresh status').output
              )
          ).value
      ).status,'Failed'
  )
  
  
• Within the True condition add the Fail activity
• You can either enter a default fail message or try to get the error from the Web Activity with an expression like this:
  @string(
      json(
          first(
              json(
                  string(
                      activity('Get dataset refresh status').output
                  )
              ).value
          ).serviceExceptionJson
      )
  )
• Last task is to pick an error code. This will show up in the logs.

Fail pipeline if refresh fails

Conclusion

In this long post you learned how to refresh a Power BI dataset from within your ETL pipelines. Hopefully you weren't scared of due the number of steps. In a next blog post we will show you how to use the workspace name and data set name instead of their IDs because that is much more user friendly and even a little safer because when you delete a dataset and publish it again it will get a different ID. This will need three additional tasks for the translation part.

Translate names to ID's

Another extension that we will show in yet an other blog post is how to refresh only certain tables within that dataset. This will cause some changes to the Body of the refresh call, but also an additional Tenant setting change and... Power BI Premium.

thx to colleague Sam Hughes for helping. 

Update: we have also posted a version without using a Service Principal. Instead it uses the MSI of ADF or Synapse.

Release Power BI via Azure DevOps extension

Case
This month Microsoft released a new DevOps extenstion for Power BI: Power BI automation tools. How can you use this?

Power BI automation tools in DevOps

Solution
The DevOps support for releasing Power BI reports, datasets and datafows was almost non existing. You can release those assets with Azure DevOps, but you need a lot of Rest APIs and even more PowerShell code. There are a few third party / open source addons, but adding those to DevOps is not always permitted by companies. An other issue is that most of them are very basic and not sufficient for all requirements.

Now Microsoft finally released its own Power BI extenstion for DevOps that shoud convince most companies to install this add on to Azure DevOps. To use this new extension go to the Marketplace and click on the Get it free button. If you have sufficient rights in DevOps then you can select the project to add this extension to. Note that it is still in Public Preview.

There is one more downside for those without a PBI Premium license. It uses the Power BI Deployment Pipelines which is a Premium feature. For those unlucky ones there is still the Rest API/PowerShell solution.

Hopefully this new extension will combine the Power of DevOps, which is often already used by companies, with the release features of Power BI. In this new series of posts we will show both solutions and compare them.