I know when I started trying to use the Power BI APIs, I was unsure of what I was doing, and how I could get it working.

Not only that, but I could see that as with building any cloud platform the APIs have to be there before they can put the skin on and make it easy to use.

There is always new features or functionality that is available via the Power BI APIs.

In this series of Blog posts, I am going to show how you can use additional features in Power BI by using the API and PowerShell.

Let me start by saying that I am no coding or scripting expert.

My goal in this blog post, is to show that using the PowerShell scripts can be fairly easy, once someone (me, choose ME) has shown you how to put in the required details.

Example

As always, I like to use an example, which I find makes it easier to follow.

In this example, I am going to be using the Power BI Refresh API to refresh a dataset using PowerShell.

My dataset will be “Fourmoo Google Analytics”

Getting the details required for the PowerShell Script

I got the details on how to refresh a Power BI Dataset from the following location below. Here it explains what the Power BI API expects to receive in order to refresh the dataset.

https://msdn.microsoft.com/en-us/library/mt784652.aspx

I then downloaded the PowerShell Script from here: HTTP

Would you believe that there are only 3 pieces of information required in order to update the PowerShell Script and get it running?

Below I will explain how to get this data and put it onto the PowerShell Script

Registering for a ClientID

The first step I had to do was to register for a ClientID

  • To do this I went to the following URL:
  • https://dev.powerbi.com/apps
  • If I was not logged in I would be prompted to log in with my Power BI Account.
  • I put in the following below in order to get the ClientID that I need in my PowerShell script.
  • I then clicked on Register App
  • Once that completes I then got my ClientID
    • It will be displayed in the ClientID section below, which I then copied and pasted and stored in a secure location.

GroupID

NOTE: Group is what it was called in Power BI before it was changed to App Workspaces. So when anything refers to Group, it is actually now referring to an App Workspace.

This is the Group where your dataset currently is in.

To find the GroupID you can complete the following steps below.

  • Log into the Power BI Service, and then I went to the App Workspace where I have the dataset that I want to refresh.
  • In my example I went into the “Site Analytics” Workspace
  • Then in the URL you will see that it will say groups, and the section immediately after that is the GroupID
    • As you can see above my GroupID starts with 0084
    • Make a note of the GroupID

DatasetID

Next is where I needed to find the Dataset ID, which I show in the steps below.

  • I made sure I was already in my Site Analytics App Workspace.
  • Then I clicked on Settings and then Settings and then Settings again
  • I then clicked on Datasets, and then clicked on the dataset that I want to refresh.
    • As in my example it was called “Fourmoo Google Analytics”
  • Now in the URL I now had my DatasetID which is which is after datasets, and highlighted as shown below.
  • I then made a note of the DatasetID

Updating the PowerShell Script

Now I am ready to update the PowerShell script with the details I got from above.

NOTE: The PowerShell script does have a lot of documentation in it, so if you get lost there is a lot of comments to assist you.

  • I opened the PowerShell ISE
  • I then opened my PowerShell script.
  • Now where it says $groupID I put in my GroupID that I had noted earlier.
  • Then where it said $datasetID, I put in my DatasetID that I had noted earlier.
  • The final piece is to put in the $clientID, which I had noted earlier
  • Then I saved the PowerShell Script file

Testing the PowerShell script and my dataset refresh

The final step is to now run the PowerShell script, ensure that it runs, and my dataset actually gets refreshed.

  • As you can see my last refresh is shown below
  • I then run the PowerShell Script.
  • I got a prompt asking for the App Permissions
    • I clicked Accept
  • Then I got the following result showing it was successfully executed
  • I could now see that it was In progress
  • And finally, it was successfully refreshed.

Conclusion

Whilst this has been a bit of a longer blog post, I do hope that you can see that if you follow the steps how you can leverage the Power BI APIs for your datasets.

I do plan on doing additional blog posts on the different Power BI APIs. If there are any that you are interested in, please leave a comment below.

And as I say in every blog post, please leave any comments or questions.

Link to the PowerShell Script here: ManageRefresh.ps1

7 thoughts on “Using the Power BI API with PowerShell scripts – Refreshing your dataset”

  1. I was actually looking for information on Power BI REST API & Powershell. Glad I found yours. I believe mentioning the fact you’re no powershell wizard is a very good introduction. I never pretended to be one either. So we’ll settle with proficient. I also shared same feelings when I first decided to explore Power BI REST API to find out what it was all about. Maybe I’ve missed something, somewhere but there seemed to be a bit of confusion understanding – as well as clearly defining – what Power BI REST API was all about in first place. Maybe the first instinctive relation we could make was mainly embedding scenarios. On June 2017, Microsoft claimed they would offer a ‘fusion’ of previous Azure Portal – Power BI Embedded option with Power BI REST API into one consolidated API. I’d like to add my 2 cents in saying they simply decided to remove what was formerly known as Workspace Collection topology from the map. The need to have a Premium also raised a big debate but I’m not getting into that… The deeper I went, the more I realized Power BI REST API offered a lot more than just embedding options. So I guess as usual… it was mainly up to us to find out what Power BI REST API and Powershell combination can really perform in real context through personal initiative R&D and testing. I do have respect for Microsoft Power BI team though I would have loved seing more consolidated information on Power BI REST API with Powershell. Not having to parse a little bit here and a little bit there…Well you know what I mean. Anyway thanks for sharing and keep on your good work. It’s appreciated.😉👍

    1. Thanks for the comments and you have some great insights into how it could or should work.

      From my side, the way I see it is that they have to build the APIs in order to get Power BI Working. Now it appears that they are starting to expose a lot more of the APIs for Power BI, as well as giving PowerShell scripts to use the APIs. And in my opinion this then makes it easier to do thing pragmatically using PowerShell.

      1. what dose “ensure that it runs” means?
        I have followed all the instructions provided by you. And it have successfully run and got the result as shown in screeshot of powershell. But I did not find any record in refresh history of dataset.
        Please help me go through this.
        Thanks.

        1. Hi there,

          It appears that it is running because it completes with no errors.

          The recordset is returned as JSON so you would need to convert it to view the data

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.