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.
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;
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 “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;
I have created a duplicate of the Country column to have the original beside this new column. here is the result:
Now I can use Add Prefix under the Transform tab, Format, and Add Prefix.
The Prefix I add is the URL before the country name, because the country name comes from the column itself.
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;
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 “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.
using the “.png” as the suffix. and here is the my final table’s data;
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).
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;
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.