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#.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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
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:
|
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:
Reference
Azure Functions
Solving DAX Time Zone Issue in Power BI – Reza Rad
Generating JSON In Power BI And Power Query – Chris Webb
Web.Contents authentication in the Power BI service – Power BI forums
Maybe I am not understanding this correctly but this function call will be fired for every single record to determine the hour offset based on whether the date is in/outside of daylight savings ?
Seems like a huge overhead particular if you are dealing with thousands of records, which means thousands of server calls??
You are correct that it would not be a good solution to call a function for every row. This post was meant to demonstrate the possibility of calling an Azure Function from Power BI, and to highlight the deficiency related to Daylight Savings Time in Power BI time functions.
Less frequent function calls could be a viable solution. For example, calling a function to get an accurate timestamp during a dataset refresh.