Using the Power Function.InvokeAfter to determine how long to wait between API calls

I have been working on a dataset which I will hopefully reveal soon, but part of that was that I was getting rate limited when making an API call.

I found Chris Webb’s insightful blog post (Using Function.InvokeAfter() In Power Query) where he details how to use the Function.InvokeAfter. The one key piece that I personally found missing was how to use this with an existing function that I had created.

I then got another fantastic question from the Power BI Community where they were looking to do an IP Address Lookup. And there are a lot of sites who offer this, but they do limit the rate at which you can query the API (Which I think is perfectly understandable considering they are offering it for free!)

My blog post shows how I ensured that I did not exceed the rate limit for the API using the Function.InvokeAfter

NOTE: I am not going to cover how I converted the IP Address to a location, I have done this previously in (Power BI Query Editor – Getting IP Address Details from IP Address)

Using the Custom Function

I am starting off where I have already created the Custom Function in Power Query Editor. I also have got a table with IP Addresses.

  • I have used a sample file in which I made up the IP Addresses as shown below.
  • I then went into the Add Column in the Ribbon and clicked on Invoke Custom Function
  • This brings up the Invoke Custom Function
    window and I put in the following information as shown below.
    • As you can see from above, I gave my new column a name of Details
    • I then clicked the drop down and selected my function I created earlier called fx_GetIPAddressDetails
    • And then finally the crucial
      part is where I selected
      my IP Address Column.
    • I then clicked Ok.
  • When you do this it returns a table as shown below.
  • Click on the Expand
    Table Button on the top right hand side, which will then prompt you which columns you want to select
  • I then selected Country
  • And below were my results.

Adding in the Function.InvokeAfter to limit the rate at which I query the API

I am now going to modify the step where I Invoke the Custom Function to limit how long it waits between API calls.

  • I created a new Parameter called “Interval (Secs) as shown below.
  • I then went to the following step in my table, and clicked on the Step called “Invoked Custom Function”
  • Then in the formula bar I have got the following.

= Table.AddColumn(#”Changed Type”, “fx_GetIPAddressDetails”, each fx_GetIPAddressDetails([IP Address]))

  • Next, I made the following changes to the above code using the Function.InvokeAfter

    = Table.AddColumn(#”Changed Type”, “fx_GetIPAddressDetails”, each Function.InvokeAfter(()=>fx_GetIPAddressDetails([IP Address]), #duration(0,0,0,#”Interval (Secs)”)))

  • I added the Function.InvokeAfter(()=>
    before I called my function fx_GetIPAddressDetails which is highlighted in BLUE above
  • I then put in the #duration(0,0,0,#”Interval (Secs)”))) which is highlighted in PURPLE above.
    • Within the #duration I also used my Parameter called #”Interval (Secs)”
      which is highlighted in ORANGE.
    • This allowed me the flexibility to change the rate limit timing without having to go into the code.
  • Now when I refreshed the data it will wait 2 seconds between each API Call.

I hope that this has been useful and an easier way to ensure that you can limit how quickly you call an API

As always please leave any comments in the area below.

Here is a copy to the PBIX file that I used in the blog post above: FourMoo – Loading IP Addresses with an Interval.pbix

BI-RoundUp – Power BI (Service & Mobile Update – Create Power BI reports from Excel & CSV Files in SharePoint Online)

Here is the weekly BI-RoundUp, so let’s get into it

Power BI – Service & Mobile Update

As you can see above there now is an option to share a report with the current filter and slicers that are enabled. This is great because it will mean that you can have a subscription report that will send it out every time with the applied filters and slicers.

There is the updated Gateway connections a lot easier with greater visibility to show you what you need to do to get it working.

The On-Premise Data Gateway has been updated to the June Edition of the Mashup Engine.

And finally, for the Mobile Report canvas they have increased the amount of tiles to be larger and longer.

You can find more details here: Power BI Service and Mobile June Feature Summary

Power BI – Create Power BI Reports from Excel or CSV Files in SharePoint Online

I was not aware that there have been updates in SharePoint online, where if you have got an Excel file with a table formatted or a CSV file, there is the option to now Open in Power BI.

This will then open it in Power BI and automatically be connected to your SharePoint Online files. Which in turn means when you update the files on SharePoint online, it will automatically be updated in the Power BI Service without having to use the On-Premise Data Gateway.

You can find the full blog details here: New feature: Create Power BI reports from tables in Excel files and CSV files

Creating a Slicer that also contains a DAX Measure

I got an interesting question from Vijay asking, “How do we do a slicer with data count?”

This is what Vijay was looking to achieve as shown below and I always enjoy a good challenge.

As I always like to do, is to show how I got to the required result.

  • I currently have got data from my website, which I look at daily to see how my blog posts are tracking.
  • In my dataset I like to see where in the world people are coming from to visit my blog or website.
  • Based on the requirement from Vijay I created a slicer which takes the total sessions per country, which I can then use as a Slicer on my data.

The first thing that I did was to create a calculated table which will have the information I require for the Slicer.

In Power BI Desktop I went to the Modeling Ribbon and clicked on New Table

This will be the Country and Sessions. I created it with the following DAX Syntax below.

Slicer Table =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'All Web Site Data'[Country] ),
        "Sessionszz", [Sessions]
    )
  • Line 1
    • What I have done above is created a table called Slicer Table
  • Line 2-3
    • This is where I added the ADDCOLUMNS Syntax for my table.
    • NOTE: Thanks to Maxim for commenting and letting me know that I did not need the original CALCULATETABLE
  • Line 4
    • I then used the SUMMARIZECOLUMNS DAX function and put in the ( ‘All Web Site Data'[Country] column, as per my requirement to get a Count of Sessions per Country.
  • Line 5
    • This is where I created my Column Name called “Sessionszz” and my measure called [Sessions]
    • NOTE: When I use measures I only use the Square brackets.
  • Line 6-7
    • I then close off my previous DAX functions.

The result is I now have the following calculated table.

How I had the data in order to create a new calculated column which will have the Country with the Total Sessions. I did it with the following DAX Syntax below.

Country with Sessions =
'Slicer Table'[Country] & " ("
    & 'Slicer Table'[Sessionszz]
    & ")"

 

And the result was I now had my new column in my table showing both the Country and the Total Sessions

Next, I created a relationship from my Fact table ‘All Web Site Data’ to my new calculated table called ‘Slicer Table’ on the Country Column.

I went to the Modeling tab again, and this time clicked on Manage Relationships

I then clicked on New and selected the following as shown below.

I then click Ok and Ok again to come back to Power BI Desktop

Now I was able to create the Slicer, which shows the Country and the Total Sessions.

I have selected the Country “Afghanistan” which has got a total of 4 sessions, and in my table, I can see the sessions and the date when they occurred.

Each time the dataset is refreshed the calculated table will also be refreshed which will ensure that the slicer values are up to date.

This was a great exercise and I personally think something quite handy to have when slicing data.

As always if you have any questions or comments please leave them in the area below.

BI-RoundUp – Power BI (Developer Update for June – On-Premise Data Gateway Update for June – Securing your connection between Power BI & Azure SQL Database)

I must admit that I have been a little quiet with regards to my BI-RoundUp and it is partially due to being extremely busy working. As well as preparing for SQL Saturday Sydney and my presentation at the Business Applications Summit in Seattle at the end of July.

As they always say, the show must go on, so here is my latest BI-RoundUp

Power BI – Developer Update for June

If you are using Power BI Embedded or the developer features of Power BI, there have been some recent updates.

Embed Capabilities

You can now set the slicer values through the Javascript API. This means that when the report loads it can have the slicers pre-set.

As shown in the image above, you now can use themes in embedded dashboards.

In the playground tool (which I suggest everyone go and have a look at), it is now a lot more interactive in terms of it now being able to show “Dynamic Report Layout” and “Capture and share Bookmarks”

Automation & life-cycle management

The documentation for the Power BI REST APIs has been updated in terms of their documentation which is auto generated from Swagger files. I had a look and I do like the look of how it all works.

Power BI Embedded in Azure

There is a note that Power BI Workspace collections are going to be depreciated will no longer be available.

And finally, the Custom Visuals support report themes.

All the details from the blog post can be found here: Power BI Developer community June update

Power BI – On-Premise Data Gateway Update for June

There is an update for the On-Premise Data Gateway, which includes a lot of bug fixes and improvements.

It also includes the June version of the Mashup Engine that is currently found in Power BI Desktop.

Here is a link to the blog post: On-premises data gateway June update is now available

Power BI – Data Gateway Update for June – Securing your connection between Power BI & Azure SQL Database

If you are currently getting data from Azure SQL Database, there is an option to “Allow access to Azure Services” which allows any Azure Service to be able to contact your Azure SQL Database.

In some scenario’s this is not secure enough.

In the link below it explains how to secure the connection between Power BI and your Azure SQL Database so that it can only be accessed by the On-Premise Data Gateway.

There is quite a lot to go through, which is detailed in the blog post: Securing the connection between Power BI and Azure SQL Database

Creating a DAX Calculated Column for Today, Yesterday and Next Working Day with Variables

Below was a question from the Power BI Community where the output was to have a calculated column in the Date Table, which would have Today for Todays date, Yesterday for Yesterdays Date, and Next Working Day (Being the following Monday)

The challenge was that for the “Next Working Day” it should only be for the next Monday and not all Mondays.

If you did not know you can use DAX Variables not only in DAX measures, but also in DAX Calculated columns.

I would like to mention that I had always like to step out my calculations when working in long or complex DAX measures or calculated columns. This video from Guy In a Cube with Patrick and Marco gives a great introduction on how to Debug DAX, in which they demonstrate how to use variables in DAX measures to debug the measures.

So below is my DAX calculated column with the explanation afterwards.

T/Y/Tom =
VAR TodaysDate =
    TODAY ()
VAR YesterdayDate =
    TODAY () - 1
VAR NextMondaysDate =
    SWITCH (
        TRUE (),
        'Date'[Date] - WEEKDAY ( 'Date'[Date], 2 )
            + 1
            >= TodaysDate, 'Date'[Date] - WEEKDAY ( 'Date'[Date], 2 )
            + 1,
        TODAY () - 9
    )
VAR NextWorkingDay =
    SWITCH (
        TRUE (),
        'Date'[Date] = TodaysDate, "Today",
        'Date'[Date] = YesterdayDate, "Yesterday",
        'Date'[Date] = NextMondaysDate, "Next Working Day",
        "Not Applicable"
    )
RETURN
    NextWorkingDay

Code above formatted with the DAX Formatter

  • Line 1 – This is where I am giving my Calculated Column a name called T/Y/Tom meaning Today/Yesterday/Tomorrow
  • Lines 2 & 3 – This is where I am creating a variable TodaysDate which today’s date using the DAX function TODAY()
    • With today’s date being 02 July 2018
  • Lines 3 & 4 – This is where I am creating a variable called YesterdayDate getting yesterday’s date, going back one day from today’s date.
  • Lines 6-8 is where I am creating a variable called NextMondaysDate.
    • I am then using the DAX SWITCH function which I find personally is a lot easier to use than an IF function.
    • My experience has also taught me that often an initial requirement is for two possible conditions, but once it is looked at more closely there are more than two conditions. And using the combination of the SWITCH and TRUE function makes it a lot easier to add in as many conditions are required.
  • Lines 9 & 10 – This line is quite interesting, because what I wanted to do was to find what Next Mondays date would be.
    • And if I had to put this single calculated column into my dataset I would get the following as shown below.
    • I actually did this as part of my debugging process to ensure that I was getting the right date.
    • As you can see above what this does is it gives me the date for each Monday in each Week.
      • For this current week the date for Monday is 07/02/2018
      • Whilst for next week the date for Monday is 07/09/2018
  • Lines 11 & 12 is where I am comparing to see if it is greater than equal to todays date.
    • If it is then use the Mondays Date.
    • In this example the output would appear to be the same as the DAX in lines 9 & 10.
    • The difference here is if you had to use it outside of the SWITCH context it would then result in every Monday having the “Next Working Day” which is not the required result.
  • Lines 13 & 14 – This is where I am putting in the ELSE condition which is set to take Today’s date and go back 99 999 days. Which should not be seen in the dataset.
  • Lines 15 – 22 – This is where once again I am using the SWITCH and TRUE DAX functions, because I have got multiple conditions to evaluate.
    • The first condition is where I am looking at my date table and where my calculated DAX column matches my variable for today.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/02/2018”, “Today”,
    • The second condition is where I am looking at my date table and where my calculated DAX column matches my variable for yesterday.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/01/2018”, “Yesterday”,
    • The third condition is where I am looking at my date table and where my calculated DAX column matches my variable for next Monday.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/09/2018”, “Next Working Day”,
  • Lines 23 & 24 – This is where I close off my variables and use my final variable called NextWorkingDay

So now when I look at my date table I see the following below which what is I wanted to get.

As you can see above there is a previous Monday’s date, and it says Not Applicable, so I am getting the results I expect.

I hope you found this blog post useful on how to use DAX variables in DAX calculated columns, as well as how to use it for debugging your DAX code.

As always if you have got any questions or suggestions please leave them in the comments section below.

Power BI – Publish to Web to Remove Grey area on the sides

I was recently browsing the Power BI Forums, where I enjoy helping others with Power BI questions. Not only that, I also learn from other people in the community.

In this instance I was unaware that Lukasz Pawlowski had created a really useful site where I could put in the page details for my Publish to Web page, which would ensure that it fits nicely into my web page, without having the grey area’s on the side.

https://lukaszpawlowski-ms.github.io/Optimize-Publish-To-Web/

How to view my Publish to Web Page without the Grey Bars on the sides.

This is what my current embedded Publish to Web page looked like on my web site.

As you can see above, there are the grey bars at the top and the bottom. Not only that, it also does not fit the page correctly.

I went to Lukasz web page and put in the following as shown below.

I then clicked on Optimize, and this is what it looked like below.

There is a lot of additional information in the code, and I am not even going to try and figure out what it is doing. But I am going to assume it is doing some smarts with regards to ensuring that it fits into the page.

Now as you can see below I now have got my Embedded report fitting in exactly. Without the grey bars. I must be honest it did take a lot of minor tweaking to get it exactly right. Now I at least have got the exact requirements for any future pages.

I hope that you have found this useful, and a big kudos to Lukasz for creating this useful site.

As always if you have any comments or suggestions please leave them in the comments below.

How many times has my Power BI dataset been refreshed?

Have you ever wondered how many times your Power BI dataset has been refreshed? And if you are using Power BI Pro, you are limited to 8 refreshes a day.

This week’s blog post I am going to demonstrate how you can use the Power BI API to view all your data refreshes for a particular dataset.

If you need a recap here is a link to my previous blog post Using the Power BI API with PowerShell scripts, in which I demonstrated how to refresh your Power BI dataset using PowerShell and the Power BI API.

If you are looking for information on how to get the GroupID, DatasetID & ClientID, please refer to my previous blog post Using the Power BI API with PowerShell scripts

Example

For this particular example, I am busy working on a presentation for SQL Saturday Sydney (which I would recommend going to, if you are in Sydney on 07 July 2018)

Part of what I am doing is refreshing the data using an Azure Function, Microsoft Flow and Power Apps (Which will be another blog post), and I wanted to know how many times I have refreshed the dataset.

I need to know if I am going to hit my 8 times a day refresh limit when testing! This PowerShell Script and Power BI report came in handy as it allowed me to know if I was going to exceed my refresh limit.

As with all examples you can download the PowerShell file here: PUBLIC – Power BI Dataset Refresh with Output to CSV.ps1

NOTE: Before starting you will have to have updated the PowerShell script where YOU have put in your GroupID, DatasetID and ClientID

Updating PowerShell Script for Data Refreshes

The only actual change that I made was to put in the location of where I wanted to store my CSV File.

This is on line 93

Once I had updated the above to a folder and file location I ran the script.

Below is what it looked like once I opened the CSV File

It is interesting to see that it also tells you what Refresh Type was done. In this instance I was doing it via an Azure Function which was making the API call.

Power BI Report with output from CSV

What I then did was I saved the CSV file into my OneDrive for Business folder location. This enabled me to load the CSV file into an existing Power BI report.

As you can see below, I now know how many refreshes I have completed on my dataset.

Wrapping Up

I have demonstrated how I leveraged the Power BI APIs to download how many refreshes my dataset has had.

And then being able to report on this allowed me to be able to know how many refreshes I had left.

Once again here is the PowerShell Script that I used: PUBLIC – Power BI Dataset Refresh with Output to CSV.ps1

If there are any comments or suggestions, please leave them in the comments below.

Bonus – If you are interested in what the PowerShell Script is doing?

Below are additional details on how I got the data down from the Power BI API call

  • Lines 82 – 83
    • This is where I am creating the URI and sending it to the Power BI API
  • Line 85
    • This is where I am getting the output from the API Call and converting it to Json
  • Line 87
    • I am then converting it back from Json. This might seem a bit counterintuitive, but my logic is when converting it from Json I am then able to Query the Json.
  • Line 89
    • Because I am converting it from Json, I am now able to select specific Json properties.
    • On line 89 I am then selecting the “value” property, which is the data that is returned from the Power BI API call.
  • Line 91
    • I am selected the specific items from the “value” property
    • This has all the details that I require for my data refreshes.
  • Line 93
    • I am then exporting the data to a CSV file.

Power BI Infographic Update – June 2018

There have recently been quite a few changes and updates to Power BI, so in this blog post I have updated my Infographic. As well as put in references to where you can find more details on the updates that I have made to the infographic.

Below are the relevant links

Filter and Sort in the Data View in Power BI Desktop

Dashboard Themes in the Power BI Service

Persistent Filters for reports in the Power BI Service

Custom Data Connectors using the On-Premise Data Gateway in Personal Mode to refresh data

Incremental Data Refresh currently for Power BI Premium Customers

BI-RoundUp – Power BI (Power BI Desktop June update – Subscribe others to Email Subscriptions – Developer Community for May, Service & Mobile Update)

Here is my weekly BI-RoundUp, and once again the awesome people from the Microsoft Power BI Team have released some additional gifts.

Power BI Desktop June Update

Here is another month of big updates to the Power BI Desktop.

Reporting

The first item is now based on your contrast settings, this will be applied to the Power BI Desktop file and in the Power BI Service. The only thing to make note of is that currently background images are not supported in high contrast mode.

There is now also more control for the Donut visual in terms of how wide or skinny the donut will be.

Along with this, there is now more control for the Pie and Donut charts with regards to the label positions in terms of where they will be positioned.

As with the line and bar visuals where you can format the measure labels, this now is also available for the Combo charts, which means that you can now format them individually for better control in terms of how they are viewed.

And finally, they have added more rows when configuring the Mobile Phone reports. It has gone from being 20 rows long to 40 rows long.

Custom Visuals

There is now a new Organizational Chart which can be used to view your data.

There is a new China Heat Map visual which will allow to view your data in China with a heatmap

Modeling

As shown in the initial image, there now is the ability to sort, and filter your data in the data view. This is a great improvement and something that I have already been using quite a bit.

It is also good to see that it has very similar functionality in terms of what you get in the Power Query Editor, where you can not only select the items, but search for items as well as advanced filters based on the data type.

If you have a different locale, there is now improved support for locale in both the Power BI Desktop and the Power BI Service.

Data Connectivity

There has been a lot of work put into the SAP BusinessWarehouse connector, where there have been improvements to the following:

  • Improved performance
  • Ability to retrieve several million rows of data, and fine tuning through the batch size parameter
  • Ability to switch execution modes
  • Support for compressed mode, which is especially beneficial for high-latency connections or large datasets
  • Improved detection of Date variables
  • [Experimental] Expose Date (ABAP type DATS) and Time (ABAP type TIMS) dimensions as dates and times respectively, instead of text values.
  • Better exception handling, so errors that occur in SAP BAPI calls are now surfaced.
  • Column folding in BasXml and BasXmlGzip modes. For example, if the generated MDX query retrieves 40 columns but the current selection only needs 10, this request will be passed onto the server to retrieve a smaller dataset.

The Spark connector now supports Windows Authentication

There has also been a lot of updates to the OData V4 connector, where it has improved support for complex data types. There is also now Open type navigation which supports extra navigation properties. Improved support for Custom URLs. Significant performance improvements. And greater resilience when loading data, where it will first try query folding, and if that fails it will then try with less query folding until it succeeds.

On the ODBC connector there has been improvements where if your underlying data source supports the TOP operator, it will then fold that down to the data source. As well as if the underlying DSN connects to a catalog, this will be shown in the Power Query Editor, so that you can select the required catalog.

And finally, there is an update if your account has been provisioned in more than one National Cloud, when signing up you can select which cloud you want to use.

All the blog post details, as well as the video by Amanda can be found here: Power BI Desktop June Feature Summary

Power BI – Subscribe others to Email Subscription

You now can subscribe other users in your organization to email subscriptions, so that they do not have to do it themselves.

I know this is something that I personally have been waiting for and so happy that it is finally here!

The only thing to note is that if your Reports or Dashboards are in a standard App Workspace, you can only subscribe other users who have a Power BI Pro license.

Whilst if your reports or dashboards are in a Premium Workspace, you can the subscribe any users, security groups or distributions lists regardless of whether the user is a Pro user or not. This is because in a Premium App Workspace you can share your reports with a free or pro Power BI User.

All the details on the blog post are here: Subscribe others to email subscriptions in Power BI

Power BI – Developer Community Update

You can now set the background colour when you embed your Power BI Application into an application, where there is extra space.

Now when you load your Power BI report in an application you have the option to load it on a specific Bookmark, so that the users can see the report in the bookmarked state.

There is now an easier and faster experience to set up and embedding environment for Power BI Embedded. This process now means that you can create the Application and upload a PBIX directly as part of the embedding creation.

And finally, there is a new Sample Application from Ted Pattison, which is on GitHub and shows the basics to embed a report. This is something that I am certainly going to look into (when I get a gap).

You can find the blog post details here: Power BI Developer Community May Update

Power BI – Mobile & Service Update

PIC

As you can see below there have been a lot of updates to the Power BI Mobile App and Service, which have already been covered in previous blog posts by Microsoft. Here is the list below, with hyperlinks if you want to get more details.

The only one that was new to me was the updated workflow invite for B2B reports, which is great to see that the experience is easier to complete.

Using the Power BI API with PowerShell scripts – Refreshing your dataset

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