SQLServerCentral Article

How to work with web information, maps, and images in Power BI

,

Introduction

Sometimes, we need to work with images in Power BI. This tip will be about the FIFA 2022 World Cup. We will create a nice report about the World Cup’s best players. The report will have the name of the players with some statistics (goals, accuracy, etc.). If I select a player, the country will be displayed in the report and also the image in Power BI.

Images in power BI

If I select another the player’s picture and country should be displayed.

Change image in Power BI dynamically

In this article, we will learn the following things:

  1. First, we will import data from the BBC website into Power BI.
  2. Secondly, we will modify the column names, and remove some extra columns.
  3. Thirdly, we will modify and convert some data which was not imported successfully.
  4. Create a Table with data.
  5. Also, we will add a map to show the country on it.
  6. Finally, we will visualize images in Power BI with a new visual.

Requirements

  1. First, you need to have Power BI Desktop installed.
  2. Finally, you need Internet access.

Import data from the BBC website

First, in Power BI select the Get data option.

Image in Power BI - Get Data

Secondly, in the Get Data window, write web in the search text and select the web option and press the Connect button.

Image in Power BI - Web

In the URL, write the following URL and press OK.

https://www.bbc.com/sport/football/world-cup/top-scorers

Select Basic

Table 1 shows the data imported. However, you will notice that the name, and country have some problems and you may need to split the data.

Problems importing data

However, there are some suggested tables that are easier to work with:

Transform data

We will select Table 2 and press the Transform Data button to modify it.

Modify the column names, and remove some extra columns

First, we will remove columns with duplicated data. There are 2 columns with the players’ full names. We will remove that column using the right-click and selecting the Remove option.

Remove column

Also, we have the country name twice. Right-click in the column and select the remove option to remove the duplicated column.

Remove duplicated field

In addition, note that the column names are column3, column4, column5, etc.

Column names

If you go to the website, you will notice that the column names are different. Double-click the column header and write the correct column header.

Website columns

Rename the column headers in Power BI according to the data in the previous image.

Rename

Modify and convert some data which was not imported successfully

Note that the Goals per 90 minutes removed the decimals when the data was imported into Power BI.

Decimals removed

If you have a similar problem, you need to divide the column values by 100. To do that we follow these steps:

First, select the Add Column option in the menu and then the Custom column icon.

Create custom column

Secondly add a name for your new column and select the Goals per 90 column from the Available columns section and divide it by 100 and press OK.

Create formula

Finally, close and apply your changes.

Create a table with data

First, in the Fields pane, check the country, full name, goals, goals per 90, mins per goal, Played, shot accuracy, and total Shoots columns.

Add fields

To add a title to the table, select the Format your visual icon and go to Title. Add a title to your table and make it bold and use a font size of 18.

Add title to table

Finally, take a look at the table. It will look like this one:

Title added

Add a map to show the country on it

In order to add a Map with the nationality, follow these steps:

First, in Power BI click outside the table and select the map visual.

Add a map in Power BI

Secondly, drag and drop the Country field in the Location.

Add the country

If everything is fine, if you select the player Kylian Mbappé (a French player), France will be displayed on the map.

Select player name and get the country in the map

On the other hand, if you select the player Valencia (from Ecuador), Ecuador will be displayed.

Select the player Valencia and show Ecuador in the map

Visualize images in Power BI with a new visual

We want to see the players’ photos. To do that, follow these steps:

First, go to the Data icon.

Go to data

Secondly, create a New column.

Add new column

Also, add a column named picture. This column contains the URL of an image in the player on the internet.

New column with image url

For example, for URL for Kilian Mbappé is the following:

https://upload.wikimedia.org/wikipedia/commons/thumb/5/57/2019-07-17_SG_Dynamo_Dresden_vs._Paris_Saint-Germain_by_Sandro_Halank%E2%80%93129_%28cropped%29.jpg/330px-2019-07-17_SG_Dynamo_Dresden_vs._Paris_Saint-Germain_by_Sandro_Halank%E2%80%93129_%28cropped%29.jpg

In addition, we will need to Get more visuals to get a visual to view images.

Get more visual to see images in Power BI

In the text box search for the word simple. Select the Simple Image visual.

Visual to handle images in Power BI

Also, press the add button to add the visual.

Add visual to handle images in Power BI

Add the simple image in the report and check the picture field.

Add picture to the image visual

Go to column tools and make sure that the picture is in the Image URL category.

Change the category of the image to image URL

Finally, add the picture in the image URL

Add image visual to the report in Power BI

If everything is OK when I select the player Kylian Mbappé, France and his photo should be displayed.

Select images in Power BI

If I select another player like Messi, Argentina should be displayed and Messi’s picture should be displayed.

Select different images in Power BI

Conclusion about working with maps and images in Power BI

In this article, we saw how to import data from the web. Modify the data and then we learn how to work with maps and dynamic images using a custom visual in Power BI.

Rate

5 (1)

Share

Share

Rate

5 (1)