Forum Discussion
Helper IICreating a geographical hierarchy : Continent > Country
adrien5555 Actually there is an easy way. I replicated your scenario with Actual table (with values) and a table wth continents and countries (lookup table) and loaded it into powerbi desktop.
Go to query editor -> select values table -> Merge queries -> select matching columns from both table (countries column) -> left outer as join kind -> OK.
This will give you matching Continent from the lookup table
Community Championadrien5555 In powerbi desktop under Data view, select column, under modelling table, under Data Category dropdown you can select type.
Yes you can use conditional formatting option introduced in april powerbi desktop update to have continets for each countries (if you dont have large number of countries you can do manually for each).
Helper IIankitpatira : Great! Is there maybe a well-known formula to add a new column 'Continent' with the proper values, depending on a 'Country' column?
- ankitpatira
adrien5555 If you're familiar with DAX you can use LOOKUPVALUE function. What you can do is have a table with list of all the continents and countries (just google it i am sure you will find plenty). Import that. Create custom column and use LOOKUPVALUE function to update that column with contients with matching country names.
Let me know if you need hand and I can do a demo for you.
- adrien5555
ankitpatira : thx, really see the point here. Got a new dataset with continents and countries.
Creating a Lookupvalue is a bit too much for me at this point... Any help very much appreciated.
Is it like :
IF (country in dataset1 match country in dataset2) THEN (Field value is equals to corresponding value //continent// in dataset2)
?
Tried to create a relationship between other dataset with a 'country' field, but I got this :
- ankitpatira
adrien5555 Actually there is an easy way. I replicated your scenario with Actual table (with values) and a table wth continents and countries (lookup table) and loaded it into powerbi desktop.
Go to query editor -> select values table -> Merge queries -> select matching columns from both table (countries column) -> left outer as join kind -> OK.
This will give you matching Continent from the lookup table