Forum Discussion
Value transformation
Hi,
I have a master table with a column containing some abbreviation but I want to decode it obtaining two different columns:
| Abbreviation | Country | Roles |
| AUT-BAPB | Austria | Business North |
| AUT-BACO | Austria | Consumer |
| BIH-UBBA | Bosnia | Business South |
| BIH-UCBA | Bosnia | Marketing |
| CZE-UCBA | Czech Republic | Business Development |
| HUN-UCBA | Hungary | Consumer Marketing |
| ITA-BUBA | Italy | Business Admin |
| ITA-MARK | Italy | Marketing |
At this stage I have identified two options:
- - Create a transcoding table and two relationships to obtain the fields required
- - Create two new column with a series of IF to obtain the transormation like
ROLES = IF(Documents[aggr]="AUT-BAPB", "Business North",
IF(Documents[aggr]="AUT-BACO","Consumer",
IF(Documents[aggr]="BIH-UBBA" ,"Business South",
"TBD")))
As you can see countries are using same acronyms with different meanings, we are talking about 30 to 50 values that can change across time so they will need to be modified. From a computational POV which one is the best solution?
Can you tell me pros and cons of both solutions? Of course, if there are better alternatives are more than welcome!
Thanks in advance! 😉
Hi , Br1bn
Accoding to your description,you want to get two different columns by some rules. And now you have two options .
I recommend you to use the first way. Here's why:
(1)For the first way, you can create a relationship table to maintain your information, and create corresponding relationships, if there are later modifications, you only need to modify the information in the table. For the second type of DAX sentence that needs to be modified if the relationship is modified, it is not very well maintained for the later stage.
(2)Creating a one-to-many relationship, we can directly use fields from a table on one table as a dimension without having to write complex DAX sentences.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi Br1bn ,
Based on your description there seems the mapping is manual in the end since the first and the second abbreviation is can be different in any case.
So in general you should push as much transformation to the source or to Power Query and not to the DAX engine if possible. Compared to DAX engine when you do transformation in Power Query the result is stored in an optmized format.
So based on your input you should introdcue a mapping table in Power Query where you give the additional information for each abbreviation combination. Then you to a left outer join using the merge functionality so that with each refresh you master data will be enriched and ready to be used.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi , Br1bn
Accoding to your description,you want to get two different columns by some rules. And now you have two options .
I recommend you to use the first way. Here's why:
(1)For the first way, you can create a relationship table to maintain your information, and create corresponding relationships, if there are later modifications, you only need to modify the information in the table. For the second type of DAX sentence that needs to be modified if the relationship is modified, it is not very well maintained for the later stage.
(2)Creating a one-to-many relationship, we can directly use fields from a table on one table as a dimension without having to write complex DAX sentences.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
