Category Archives: power bi desktop

Power BI Incremental Refresh with Web API Data Source

It’s been a while since I typed up a blog post but life, even in the midst of a pandemic, has been busy and crazy. As you can imagine, that customers I support in the education industry have been forced to pivot on a dime in order to continue to operate. My customers, in school districts and higher education institutions, are proving to be nimble and adaptive by adopting Azure cloud technologies such as Power BI and the Azure data platform in a time of high stress.

But I wanted to share one of the neat things I’ve recently worked on with one of my academic medical customers. My customer came to me with a specific challenge related to their adoption of Power BI and I wanted to take a few minutes to walk through a summary of the challenge and how we solved the problem.

The Challenge

The customer is using Power BI to report on data from Service Now via APIs. So the customer was able to quickly connect Power BI to Service Now data and begin reporting on relevant datasets very quickly. The challenge, however, is that querying multiple years of data via the API was less than desirable for a variety of reasons.

The customer was interested in exploring the incremental refresh capabilities of Power BI, but were worried about using Power BI’s native incremental refresh capability since query folding (if you’re new to query folding, read this here) is not supported by Power BI’s web connector. So the customer team reached out to me with the challenge and I worked up an example that I think will help them meet their requirement.

The Solution

The solution I came up relies on the fact that the Service Now API accepts a date as a parameter. But since I don’t have access to Service Now, I used WeatherAPI.com for demonstration purposes. There are a few different APIs you can use, but in my example I’m using their history API. If you want to play along, you can register for a Free account at WeatherAPI.com which gives you access the 7 days worth of history of weather data (the docs say 30 days but I could only query 7 days worth of history).

Calling the Data Source API

The first step was to create a function in Power BI that would query the weather API and accept a date as a parameter. To query the API data source, we’ll use the Web.Contents function with the Query argument, and this is really important (Chris Webb has a very nice blog post on this that I found helpful).

Take a look at my query below and notice the Web.Contents function. Because I need to dynamically construct the URL including the query string parameters, using the Query argument allows me to dynamically add the parameters while still maintaining a valid base URL, which is important if you want Power BI to be able to validate the connection prior to the data refresh.

(dtquery as text) =>
let
Source = Xml.Tables(
Web.Contents("http://api.weatherapi.com/v1/history.xml",
[Query=[key=APIkey, q="32212", dt=dtquery]])),
#"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Name]), "Name", "Table"),
#"Expanded location" = Table.ExpandTableColumn(#"Pivoted Column", "location", {"name", "region", "country", "lat", "lon", "tz_id", "localtime_epoch", "localtime"}, {"location.name", "location.region", "location.country", "location.lat", "location.lon", "location.tz_id", "location.localtime_epoch", "location.localtime"}),
#"Expanded forecast" = Table.ExpandTableColumn(#"Expanded location", "forecast", {"Table"}, {"Table"}),
#"Expanded Table" = Table.ExpandTableColumn(#"Expanded forecast", "Table", {"hour"}, {"hour"}),
#"Expanded hour" = Table.ExpandTableColumn(#"Expanded Table", "hour", {"time_epoch", "time", "temp_c", "temp_f", "is_day", "condition", "wind_mph", "wind_kph", "wind_degree", "wind_dir", "pressure_mb", "pressure_in", "precip_mm", "precip_in", "humidity", "cloud", "feelslike_c", "feelslike_f", "windchill_c", "windchill_f", "heatindex_c", "heatindex_f", "dewpoint_c", "dewpoint_f", "will_it_rain", "chance_of_rain", "will_it_snow", "chance_of_snow", "vis_km", "vis_miles", "gust_mph", "gust_kph"}, {"time_epoch", "time", "temp_c", "temp_f", "is_day", "condition", "wind_mph", "wind_kph", "wind_degree", "wind_dir", "pressure_mb", "pressure_in", "precip_mm", "precip_in", "humidity", "cloud", "feelslike_c", "feelslike_f", "windchill_c", "windchill_f", "heatindex_c", "heatindex_f", "dewpoint_c", "dewpoint_f", "will_it_rain", "chance_of_rain", "will_it_snow", "chance_of_snow", "vis_km", "vis_miles", "gust_mph", "gust_kph"}),
#"Expanded condition" = Table.ExpandTableColumn(#"Expanded hour", "condition", {"text"}, {"text"})
in
#"Expanded condition"

When the Power BI dataset is refreshed, the query string parameters are added to the base URL. I can see this if I take a look at the query diagnostics for the steps that invokes the function. For the weather API, I need to include the following parameters:
1. key = API key
2. q = location zip code
3. dt = date
Notice the parameters “key”, “q”, and “dt” being added to the base URL with corresponding values.

With the function created, I’m ready to create my query that will call my function and create a table in my Power BI data model that will be configured for incremental refresh.

Building the Query & Calling the Function

First, I connected to a table in an Azure SQL Database that contains a list of dates and the corresponding year, month, and day values. Part of the reason I’m using Azure SQL as a data source is because it supports query folding.

Next I created the RangeStart and RangeEnd parameters which are required for using Power BI’s incremental refresh capabilities. Be aware that these parameters should be Date/Time type. Then I applied a Custom filter to the Date column based on the RangeStart and RangeEnd parameters. Be aware that the datatypes (Date/Time) for the parameter and the column you’re filtering should both match.

When applying the custom filter, ensure that you do not use “is after or equal to” and “is before or equal to” at the same time as this can cause you to have duplicated data in adjacent partitions, as noted in the docs.

Also, as a side note, I created a parameter to hold my API key which you saw referenced in the function we first discussed.

I perform a few transformations to construct the value that will be passed to the previously mentioned function’s “dt” parameter.

Then I’m ready to invoke my custom function I created in the first step. After I invoke the function and expand the contents, I end up with a view of the relevant weather data.

I clicked Close and Apply and the data is loaded into the model.

Configure the Incremental Refresh Policy

Now I’m ready to configure incremental refresh. I won’t go through the steps of configuring incremental refresh but it’s clearly documented here. In the below screenshot, you’ll see how I configured my incremental refresh policy. I want to keep 7 days worth of history and only refresh the 3 days previous to the current day. This means that when I first publish the dataset, Power BI will refresh all 7 days worth of data to load the dataset. Then on subsequent refreshes, only the past 3 days will be refreshed. Days older than 7 days will be dropped from the dataset.

Notice the warning above which states “Unable to confirm if the M query can be folded. It is not recommended to use incremental refresh with non-foldable queries.” I suspect this warning is appearing because of the function we’re using to call the API. In these scenarios, the documentation states that we should validate that the incremental refresh is occurring correctly and data is being incrementally refreshed. In a few moments, I’ll show you how I validated only the past 3 days were being refreshed.

Publishing and Refreshing the Data

Last, I published the data model and report to the Power BI service. In this example, I’m publishing to a workspace using Power BI Premium capacity so I can connect to the workspace with SQL Server Management Studio and view the partitions that will be created by Power BI.

Now that the data model and report are published, I can see the report. At this point I only have one day’s worth of data.

To load the previous 7 days worth of data, I need to update the data source credentials. To update the data source credentials, go the settings for the dataset and click Edit credentials next to each data source in the Data source credentials menu.

For my SQL Database I entered my SQL credentials and for the Web data source I used anonymous authentication but clicked the checkbox to Skip test connection because the connection will fail until the API key is provided (which happens in the function at the time of querying).

Verify the Incremental Refresh is Working

Because this workspace is on my Premium capacity, I can enable the XMLA endpoints to allow connectivity to the workspace with other tools. So before we process the data model, lets connect to the workspace and take a look at the partitions with SSMS.

As you can see, the WeatherData table only has the single, default partition. Now lets go back to Power BI and kick off a refresh of the dataset. Once the refresh completes, lets re-check the partitions in the WeatherData table in SSMS. You can see that we now have 8 partitions (7 days of history + the current day) and each partition has 24 rows. Power BI has created these partitions based on the incremental refresh policy we defined in the previous steps. Cool stuff!

What do you think will happen now if I refresh the data again? Lets find out! After the data refresh completes, I check the Last Processed timestamp for the partitions. Notice that only the last 3 partitions have been processed based on the timestamp! This indicates to me that my incremental refresh policy is now working correctly. I’m ready to schedule the data refresh in Power BI based on my requirements.

I can also check the Analytics on my API calls against WeatherAPI.com and see the additional API calls show up.

The Summary

In this blog post, I shared some steps for incrementally refreshing data in Power BI with data sources from a web API. If you’d like to take a look at my Power BI template that includes the logic, you download the .pbit file here.

Here are some of the resources I shared inline the blog post.

Chris Webb’s blog post on Web.Contents: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
Miguel Escobar’s blog post on incremental refresh with SharePoint Online data source, which I found helpful because it follows a similar approach: https://www.poweredsolutions.co/2020/01/19/incremental-refresh-for-files-in-a-folder-or-sharepoint-power-bi/
What is Power BI Premium? https://docs.microsoft.com/en-us/power-bi/admin/service-premium-what-is
Incremental refresh with Power BI: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh
Dataset connectivity with the XMLA endpoint: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools
Recording Query Diagnostics in Power BI: https://docs.microsoft.com/en-us/power-query/recordingquerydiagnostics
Download SQL Server Management Studio: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
Configure schedule refresh with Power BI: https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh
WeatherAPI.com: https://www.weatherapi.com/

I hope you found this blog post useful! If you did, leave a comment and let me know and share it with your friends and teammates!

Making Sense of the Power BI Ecosystem Presentation Materials

I just wrapped up my first presentation on Making Sense of the Power BI Ecosystem at SQL Saturday #755 in Davie, Florida at Nova Southeastern University. The room was full and unfortunately people had to stand or sit in the back, but the audience was great and we had lots of fantastic questions!

Download the presentation materials for Making Sense of the Power BI Ecosystem

As I mentioned during my session, you can download my Power BI Architecture Diagram hereContinue reading Making Sense of the Power BI Ecosystem Presentation Materials

Converting a Power BI Desktop File from Import to Live Query

A customer of mine is in the midst of a proof of concept using SQL Server and Power BI. During the POC, all the modeling was done in Power BI Desktop. Now that the POC is coming to the next phase, the customer is ready to move the Power BI data model to Analysis Services. But the problem is that all the visualizations in the Power BI Desktop file based on the imported data model will need to be recreated in a new Power BI Desktop file using a Live Query connection to Analysis Services. If the visualizations and reports are extensive, this could be quite a bit of work.

In this blog post, I’m going to walk you through modifying a Power BI Desktop file with an imported data model to use an external data model hosted in Azure Analysis Services or SQL Server Analysis Services 2017. This isn’t supported by any stretch of the imagination but if you’re in a pinch and have to convert a Power BI Desktop file from an imported data model to Live Query then this may be helpful to you. Also, this method works as of the January 2018 release of Power BI Desktop but there’s no guarantee that this method will work in future releases of Power BI Desktop. Continue reading Converting a Power BI Desktop File from Import to Live Query

Preparing for Microsoft Certification Exam 70-778: Analyzing and Visualizing Data with Microsoft Power BI

Late last month Microsoft released the certification exam for Power BI, 70-778: Analyzing and Visualizing Data with Microsoft Power BI. The exam is currently in beta and will likely remain that way for the next eight to twelve weeks. If you were lucky enough to snag one of the 300 uses of the voucher code, you’re already registered for the exam. If not, you’ll be waiting until the certification exam’s beta period is wrapped. And then everyone will be able to complete the exam. Thankfully I was able to take the exam a couple of weeks ago but am still waiting on the results for the next few weeks.

If you’re fairly new to Power BI or don’t have much experience with the Power BI ecosystem, I’d recommend starting with these helpful self paced learning options. Continue reading Preparing for Microsoft Certification Exam 70-778: Analyzing and Visualizing Data with Microsoft Power BI

What-If Analysis in Power BI Desktop

One of the most talked about features previewed during the Microsoft Data Insights Summit in the previous June was the What-If Analysis capabilities. Today, this capability was finally released as part of the August update to Power BI Desktop.

New to Power BI? Start here!

The What If Parameter feature allows you to dynamically perform what-if type analysis by using a slider bar to visualize changes to a calculation. This becomes a very flexible way to perform all kinds of different “what if” type scenarios.

how to set up what if analysis power bi

In this blog post, I’m going to walk through setting up a simple What-If type scenario to dynamically change a Revenue Target calculation. Continue reading What-If Analysis in Power BI Desktop

Power BI NFL Football Stats Comparisons and Analysis Report is now available!

Important Update: View the latest version of my NFL stats analysis Power BI Report here

For the past few years I’ve combined my love of professional football and analytics by releasing a series of Power BI reports featuring player statistics. This year is no different. This year I’m finally able to release my NFL Football Stats, Comparisons, and Analysis reports featuring the stats for players at the quarterback, runningback, wide receiver, tightend, and kicker positions. Unlike previous years, this years reports are based on data from NFL.com. My goal for producing this report is, as nerdy as this is, to give me a leg up on my fantasy football drafts. If you’ve ever played fantasy football, you know the key to winning is having the deepest roster and I hope that these reports will allow me to identify middle and late round talent using the collected data in a readable and navigable format.

I think this is probably my best version of the NFL stats report that I’ve released yet, and there’s a few reasons why I think so. First, I included Continue reading Power BI NFL Football Stats Comparisons and Analysis Report is now available!

Power BI Architecture Diagram v3 is Now Available!

Download the latest version of the Power BI Architecture Diagram here!

Over the past couple weeks there has been a ton of news and announcements around the direction Microsoft is taking Power BI. Between Power BI Premium and Power BI Report Server, my head has been spinning trying to stay educated on all the new changes and enhancements. With all these announcements, it was definitely time to update my Power BI architecture diagram to reflect the addition of Power BI Premium and Power BI Report Server.

Here’s how to install and set up Power BI Report Server (Preview)

I’ve made a few changes in v3 of the Power BI architecture diagram. Continue reading Power BI Architecture Diagram v3 is Now Available!

Microsoft Business Intelligence Materials from Discussion at Jacksonville University

Today I had the wonderful pleasure for leading a discussion regarding the products, services, and tools Microsoft provides in the business intelligence stack. I met with Jacksonville University students and faculty and also had the pleasure of speaking with members of local businesses that are interested in leveraging Microsoft technologies. It was a wonderful experience and I had a great time!

If you’d like to download my slide deck from the discussion and presentation today, you can find that here.

Continue reading Microsoft Business Intelligence Materials from Discussion at Jacksonville University

Introduction to Power BI Desktop – Session Materials Available

imageFor my second time, I was afforded the great opportunity to present virtually to the great people of the Madison Power BI User Group. I’m always really excited when someone invites me back a second time to speak because that means that the first time wasn’t completely awful! We had a great time last evening discussing Continue reading Introduction to Power BI Desktop – Session Materials Available