Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!!!! 🙂
Solved! Go to Solution.
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! | |
#proudtobeasuperuser | |
@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 🙂
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! | |
#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 🙂
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |