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

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

Power Query Pattern – Adding Spaces in Text within your data with Camel Case

In this week’s blog post, I created this Power Query Pattern, which I created to add in spaces for CamelCase text within a column.

To get this to work for you, all that you do is need to make one change to the code.

Below is what the data looked like

Then I created the following Power Query Pattern below.

#"TRANSFORM - Camel Case" =
Table.TransformColumns(
#"Removed Columns3",
{{"Operation",
each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " "), type text}})

 

  • To use this pattern below are the following changes that you will need to make it work in your Power Query Editor
  • Line 1
    • This is my step name
  • Line 2
    • This is where I am using the Table.TransformColumns
    • NOTE: Even though this does appear to only be used for transforming columns, it works for data within a column.
  • Line 3
    • This is referring to the previous step name, which is returning the table contents
  • Line 4
    • This is my column name where I want to add in the spaces. As with my image above the column was named “Operation”
    • NOTE: This is the only part of the pattern that needs to be changed.
  • Line 5

What I did to get this to work is I was in my Power Query Editor Window

I then clicked on Advanced Editor in the Home ribbon

I then added this step into my code as shown below.

I then clicked on Done

I went back to my column and I could now see the data with the spaces after each capital word

Conclusion

As you can see I have demonstrated how Powerful Power Query (see the multiple use of Power!) is to get the data in the shape that you require.

If you have any suggestions or comments please let me know.