Forum Discussion
Helper IICreating a geographical hierarchy : Continent > Country
Hi all,
Brand new on PBI, so please excuse these trivial questions :)
How to indicate that a column is for geo data, like countries?
It indicates for some of my csv data sources, but not for others.
Also, how to create a hierarchy of continent > country ? Is it by adding a column and an IF condition like (IF country is (US or United States) THEN 'North America' ?
Thanks a lot,
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
7 Replies
- asocorro
Skilled Sharer
In Data view, select the field and go here:
For hierarchies, just drag and drop fields on top of others in the Report view.
- ankitpatira
Community Champion
adrien5555 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).
- adrien5555
ankitpatira : 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.
