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!

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.

using System.Net;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info($"C# HTTP trigger function processed a request. RequestUri={req.RequestUri}");

    // parse query parameter
    string timezone = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "timezone", true) == 0)
        .Value;

    // Get request body
    dynamic data = await req.Content.ReadAsAsync<object>();

    // Set timezone to query string or body data
    timezone = timezone ?? data?.timezone;

    return timezone == null
        ? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a timezone on the query string or in the request body")
        : req.CreateResponse(HttpStatusCode.OK, timezone + ": " + UtcConvertToTimezone(timezone).ToString());
}

public static DateTime UtcConvertToTimezone(string timeZone)
{
    TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById(timeZone);
    return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tz);
}

// 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.

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:

{
	"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.

Call an Azure Function from Power BI Authentication

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

Call an Azure Function from Power BI