Call an Azure Function from Power BI

Call an Azure Function from Power BI

This post shows how to call an Azure Function from Power BI. This scenario offers some interesting possibilities, because it allows for integrating the power of several common programming languages with Power BI and Power Query. If you’re not familiar with Azure Functions, it’s a service that allows you to run small pieces of code without having to deal with a server.

In this example, we’ll write a query in Power BI that submits a time zone to an Azure Function. The function simply returns the current time in the specified time zone. While this is a very basic example, it is important because it allows for getting the date and time in Power BI that takes into account Daylight Savings Time. While the M language is loaded with date and time functions, to my knowledge it is not aware of Daylight Savings Time. Reza Rad has a great post on time zones titled “Solving DAX Time Zone Issue in Power BI“.

More important is the fact that HTTP requests work with Azure Functions. With some APIs, these requests work fine from Power BI Desktop and Power Query, but they fail when attempted from the Power BI cloud service. This is apparently due to how Web.Contents handles authentication in the Power BI service. See this forum post for more details. It is great that HTTP requests work with Azure Functions! This means that you could do cool stuff like use a function as a conduit to an API that doesn’t work natively with Power BI, post status updates to Twitter, etc. If you have ideas for Power BI / Azure Functions integrations, please leave them in the comments!

Azure Function

I’m not going to do a complete tutorial on creating an Azure Function, as this is pretty straightforward. I’ll point the way by saying that this is an HttpTrigger authored in C#.

HttpTrigger-C# template

Here is the function code in C#. It is made up almost entirely from the sample in the Azure Functions documentation, with the addition of a UtcConvertToTimezone method. It works by generating the current time in UTC, and then converting that to the time zone supplied by a HTTP request. The time zone can be supplied as a query parameter or in the request body.

Power BI

We’ll supply the time zone using the HTTP request body, with the request formatted as JSON. We could hard code the JSON string into the query, but it’s better to generate it to make the query more flexible. Chris Webb has shown the way like he often does with a post titled “Generating JSON In Power BI And Power Query“. I’m going to borrow his GetJson function, which is shown below.

1

2

3

4

5

6

7

// Power BI function generates JSON

(InputData) =>

let

JsonOutput = Json.FromValue(InputData),

OutputText = Text.FromBinary(JsonOutput)

in

OutputText

We’ll call this Power BI function from another query called GetDateTime. If you want to try this yourself, you’ll need to supply your own values from Azure for everything surrounded by angle brackets. In the Azure Functions site, there is a “Get function URL” link that will provide the necessary values. You can also specify a different time zone ID. The full list is here.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

let

url = "https://<function app name>.azurewebsites.net",

timezoneID = "Eastern Standard Time",

// Call GetJson Power BI function to convert record to JSON object

jsonContent = GetJson([timezone=timezoneID]),

// Submit JSON object to Azure Function using an HTTP POST

StatusUpdate = Web.Contents(url,

[

Headers = [#"Content-Type" = "application/json"],

Content = Text.ToBinary(jsonContent),

RelativePath="/api/<function name>?code=<function code>"

]

),

response = Json.Document(StatusUpdate)

in

response

The JSON string generated by the M code will look like this:

JavaScript

1

2

3

{

"timezone":"Eastern Standard Time"

}

When you first run the query, you’ll be prompted about how you want to authenticate. Anonymous authentication will be used at the root URL.

If everything is setup correctly, you should get a result that looks like this when refreshing the dataset: