Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

How to use service principle to refresh a single table in PBI dataset?

Hi team,


I learned that I can use service principle to refresh a single table in PBI dataset, that's what we need in our project, we want to automatically refresh single table in datasets base on our schedules. I plan to automate the execution of the PowerShell script via automation runbook finally.  Currently I tried 2 ways, but neither has worked, need your kind suggestion, thanks in advance.

1). Use Invoke-ProcessTable

With this method, I could successfully refresh a single table in dataset, but 2 shortcomings:

1). An authentication window will pop up when running this command at the first time

2). Could only refresh the single table under my owned datasets, it seems I didn't use service principle to refresh but my personal id.



2). This command finished without error, but the single table was not refreshed at all.


Super User
Super User

Hi @TracyQian11111 ,


Does it work if you login according to the following snippet?

    [parameter(Mandatory = $true)] [String] $sptenantid,
    [parameter(Mandatory = $true)] [String] $spclientid,
    [parameter(Mandatory = $true)] [String] $spsecret 
Write-Host "build up credentials of service principal"
$spsecretsecurestring = ConvertTo-SecureString $spsecret -AsPlainText -Force
$spcredentials = New-Object -TypeName PSCredential -ArgumentList $spclientid, $spsecretsecurestring
Write-Host "login to power bi with service principal"
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $spcredentials -Environment Public -Tenant $sptenantid


Let me know, if this works 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Hi Tom,
Thank you! Seems it works well, I could login with service principal, pls. refer to below screenshot.

@TracyQian11111 ,


Awesome! How about the refresh, did that work out, too?


Please, do not forget to accept the answer as a solution so others have it easier to find it later on 🙂


Lastly, I'd like to share ths blog post with you:


Essentially, it covers how to change the ownership of a dataset to a service principal. It uses a PowerShell script as well where I actually took the snippet from. There might be some other code pieces that help you sort things out (i.e. getting an access token for your data source, thiough be aware, in that case the owner of the dataset should be the service principal). Just thought I share it as it might helpm you anyway 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Hi Tom,

Many thanks for your suggestion and reply!

It's a pity that refresh doesn't work, would you mind to suggest what I am missing here?
I could run this XMLA query successfully in SSMS and the table could be refreshed well (with my personal id).

But if I run the same Query in PowerShell, no error message, but the table was not refreshed (even though I used the service principal to take over the dataset).

I did these preparation works before I running this PowerShell script:


1). Enabled 'read write' (under XMLA Endpoint) in PBI admin portal

2). Added service principal to Azure AD security group

3). Enabled Service Principals to use Power BI APIs in PBI admin portal

4). Added Service Principal to Power BI Workspace as Admin

5). Installed the ADOMD library


Do you have any suggestion about this problem? thanks a lot!



@TracyQian11111 ,


Could it be because you are using "Command.CommandText"?


How about if you try it with the refreshes API. Currently, I have no way to test and debug it, but maybe something like this?

Invoke-PowerBIRestMethod -Url "[workspaceID]/datasets/[datasetID]/refreshes" -Body ($body | ConvertTo-Json -Depth 80) -Method Post


with the $body like the below:



Hope we are getting closer 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Hi Tom,


Good news, seems we are closer, thanks a lot! 
I could run the script with your mentioned code above, as noticed the dataset which owned by myself will be refreshed successfully, but got error if the dataset is owned by someone else. After I took over the dataset with service principal, I got the missing credentials error message, seems after took over the dataset, I also have to setup the credentials for all the data sources with service principal, have no idea currently, need to investigate how to do.

error message.jpg


Hi @TracyQian11111 ,


That is great news!


The blog post also shows how to update the credentials by fetching an access token from Azure and then updating the source. It does it for the audience of a sql database ( It depends a bit on your sources but that could work as well?


Also, I think it'd be great if you keep posting your code so others can follow thiis. I am pretty sure this be of a huge help for other as well!



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Hi Tom,

Thanks a lot for your help and sorry for my late response.

I am struggling with how to update the credentials for source sharepoint list, not succeed yet.

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.