Add Prefix and Suffix to a Text in Power BI: Generate Country Images and pages

Add prefix and suffix to a text column in Power BI and Power Query

There are many useful transformations in Power Query for Power BI. One of them is adding the prefix and suffix to a text column. These transformations are very simple to apply and have an immense value in building columns based on patterns and template texts. In this short video and article, I’ll explain how you can use this to add links to each country’s Wikipedia page, map in Google, and also show the flag image.


Sample data

One of the ways you can learn about a transformation, is to use it in a sample that makes sense. That is why I go through a full example here. Let’s say I have a sample dataset like below;

List of countries as the sample dataset in Power BI

I populated this list from here if you are interested to follow using Get Data from Web. I am interested to have a link to the Wikipedia page of every country from my report. I realize the the Wikipedia page URL of a country looks like below;

The “” part appears before every country name, and if the country name have space in the name, it has to be replace with the “_”. So I can do that in two steps;

REPLACE VALUE: Change the space to underscore

I can do this transformation with a simple REPLACE VALUE transformation;

Replace value used to change the space characters to “_”

I have created a duplicate of the Country column to have the original beside this new column. here is the result:

the replace value results in the dataset

Add Prefix

Now I can use Add Prefix under the Transform tab, Format, and Add Prefix.

Add Prefix in Power Query and Power BI

The Prefix I add is the URL before the country name, because the country name comes from the column itself.

Adding prefix to a column to build a country’s Wikipedia page link

This results in creating the link to the Wikipedia page. As simple as that.

I can use the same approach for Google Maps, the URL for Google maps is like below;

This time, I need “+” instead of space, which means a usage of the Replace Value again, and the prefix would be everything before the country name;

Create the link to country’s map page using Add Prefix in Power Query and Power BI

Add Suffix: Image URL sample

And at last, let’s say I want to build an image URL for the flag of each country. This is what a sample URL looks like;

This time, we need “-” instead of the space in the name of the country. The “” as the prefix, and “.png” as the suffix. You already know how to do the first two parts, I can do the last part just using the Add Suffix option.

Add Suffix in Power Query

using the “.png” as the suffix. and here is the my final table’s data;

The Wikipedia page, Google map link, and the flag image url created using Prefix and Suffix


Lastly, I can present this data in the report. I just need to make sure that I set the correct Data Category for the column (read here to learn more about it).

Set data category in Power BI Desktop

The data category for the Flag column can be Image URL, and for the Map and WIKI page be Web URL. Here is the data presented in a table visual in Power BI finally;

presenting URL and images in the Power BI Desktop


There are many useful transformations for Text columns in the Power BI. Add Prefix and Suffix are two of those, which can help a lot when you want to generate a text value based on a pattern and values of a column.

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:

Leave a Reply