Where to start with grouping column data?

Hi Anonymous ,

Generally try to add new columns as far upstream as possible and as far downstream as necessary (this is Roche's Maxim of Data Transformation). And you are already thinking the right way: Do it in the source! Now sometimes, this does not work. So in your case I suggest to do it in the Power Query or Data Flow layer.

For this, add a custom column and apply exactly the pseudo code you described. 

if [citycolumn] =  "Stockholm" then "Sweden" else if  [citycolumn] =  "Oslo" then "Norway" else if [citycolumn] = "Berlin" then Germany else ...

Now, the problem is that you have to write quite some code... And what happens if a new city pops up or one of your region definitions changes, like "Stockholm" shall switch the region from "Sweden" to "Nordics". You would literally define the logic in the code which might be hard for other users to both understand and maintain.

So probably, it would be better to import data that does that exact mapping for you. Either you define it yourself in a table or an excel file or you might wanna utilise a standard table from the web (i.e. ISO standard) and load it into Power BI. As soon you have that table, just join (merge) it with your table in Power Query and add that region column.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/