Get Images from Web Page Into the Power BI Report Using Power Query

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Power Query can connect to a web page and get data from there, and I have explained it before in my article about movies data analysis. However, the default way of getting data from the web page doesn’t fetch images into your report. Images in a webpage come through their own URL. If you can use Power Query in a way that it reads that URL, then you can bring it into Power BI, and visualize it. In this article, I show you a method that you can use to fetch images from the webpage and bring it to visualization in Power BI, all using Power Query magic. To learn more about Power BI, read Power BI book from Rookie to Rock Star.

Sample Source

For this example, I am using the IMDB Top 250 movies list as a source. You can access it here: https://www.imdb.com/chart/top

You can see how the page looks like below. What we want to do in this example, is to get those images into our Power BI report.

Get Data from Web

You can use Get Data from Web in Power BI, and you’ll have the option to even customize it a bit, and use the Use Table from Examples to get part of the data from the table above.

I have explained in another article how you can get something like below from the table above:

33

The problem, however, is that still, you won’t get the images. So let’s see how we can get that.

Edit Queries

First thing you need to do is to go to Power Query Editor and remove all extra steps. Only leave the first step of reading data from that page. This might be using functions such as Web.Contents, Web.Page or Web.BrowserContent, or etc. This is a step that has the URL in it.

Then Convert it to Table:

Now you are ready to do the next step, which is searching for the HTML code of the images.

Finding the HTML code of the images in the page

There are different tools and methods to find the HTML code for the image. The one that is simple and I use often is using the Inspect feature of the Chrome web browser. If you browse to the same web page using Google Chrome, then you can right-click on one of the images, and select Inspect.

That will bring up the HTML code part of the page for that element you have right-click on. In this case, would be the source of the image!

The good part about the Inspect element of Chrome that I like is the ability to move in the code, and see which part of the page it is. If I hover a bit higher on top of the tbody class lister-list, I can see the entire table;

Breaking the Data using the HTML codes

Now that you know what code you have to search for, you can apply some transformations in Power Query to get into that part.

The first step for me is to isolate that table of all movies (I don’t start from images, because there might be other images in the page outside of that table). That table starts with tbody…. “lister-list. So the “lister-list” can be the delimiter for me to start. and then the delimiter to end is </tbody>.

So I can use Extract Text Between Delimiter

With configurations below:

Now I’ve got the table section. I can split it into rows, but using the <tr> part:

The key for Split Column is to Split into ROWS;

This will give you one row per movie, except the very first row which should be removed.

You can remove that using the Remove Top Rows then top 1 row.

Now let’s get to the image part of our table. as you can see in the code below from Inspect element of the Chrome browser, the image is within “<img src=” and ” width=”. (this combination would be of course different for other webpages)

So All we need to do is another Extract Text Between Delimiters:

And here you go, we got the URL of all images in that table:

You can use the same approach to get other parts of those movies, such as Title, the link to the movie page, etc. But let’s keep this article only focused on one subject. I got the rest of the data using simple Get data from the web and the method explained here and then merged it back to the Images, so here is my table at the end:

After Close and Apply. In the data model, I have to set the Data Category of this field to Image URL:

Now I’ve got images everywhere in my Power BI report I want:

Summary

You have seen in this article how powerful can be the get data experience of Power BI because it uses all the transformations in Power Query. For this example, I have used mostly Split Column and Extract Text Between Delimiter transformations. The key is always finding the part of HTML code you want to fetch, and then use appropriate transformations to fetch it correctly.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

3 thoughts on “Get Images from Web Page Into the Power BI Report Using Power Query

Leave a Reply

Your email address will not be published. Required fields are marked *