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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
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.

Video

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;

https://en.wikipedia.org/wiki/New_Zealand

The “https://en.wikipedia.org/wiki/” 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;

https://www.google.com/maps/place/New+Zealand

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;

https://www.countries-ofthe-world.com/flags-normal/flag-of-New-Zealand.png

This time, we need “-” instead of the space in the name of the country. The “https://www.countries-ofthe-world.com/flags-normal/flag-of-” 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

Presentation

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

Summary

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.

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.

Leave a Reply