Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply

Where to start with grouping column data?

Hi all! 

Such a basic question but after a few Google searches I'm not seeing quite what I need. 

I have a column of towns, about 30 different towns repeated in various quantities (they relate to customer addresses so multiple customers in the same town etc). 

I want to add  a "region" column, so each of these towns would belong to one of 5 regions. 

I don't want to add this into the source data because well, I can't add it to the system. So it's got to be done in the data. 

Where do I even start, I'm guessing it's a new column with a formula to say "if X,Y,Z town then A, if A, B, C town then Y..." and so forth. 

something like that? What should I use? 

thanks!!!! 🙂 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @Googlecanthelp ,

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

3 REPLIES 3

@tackytechtom Thanks for your help, I trialed the IF code and it worked in theory but as you said, that would be a lot of code to create and maintain so I've gone with a simple table added to the model with town and region. It's working perfectly. Simple but effective 🙂 

tackytechtom
Super User
Super User

Hi @Googlecanthelp ,

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Thanks so much, this actually all makes sense and yes I'd love to put it in the source but it's an out of the box product and just isn't configured with the "regions" options unfortunately. 

I like the idea of the table too, I did think this as yeah there are like 50 towns so it's a lot of code that I have to try and get right! 

Im going to give it a try tomorrow and see how it goes, I'll report back! Thanks so much 🙂 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.