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;
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;
I have created a duplicate of the Country column to have the original beside this new column. here is the result:
Add Prefix
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;
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;
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.
using the “.png” as the suffix. and here is the my final table’s data;
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).
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;
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.
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: https://radacad.com/blog.