Forum Discussion

Harish85's avatar
Harish85
Helper III
10 months ago
Solved

Formula for new column

HI Team,

 

I want to create a new column next to Partner type,

if Partner type is below values then new column(descripton) should show below values, please suggest formula or any other method.

ZZK1LTSD0074K table name, zz_partner_type is the column name.

 

Partner type         Description

RE                           BILL TO 

RG                          PAYER

WE                          SHIP_TO

EL                            END USER LOC

EU                          END_USER

ZK                           DIST

ZR                            RESELLER

Need Help
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    10 months ago

    Hi Harish85,

     

    The easiest way is to use DAX:

    • Go to Data view
    • Select your table ZZK1LTSD0074K
    • Click New Column
    • Paste the formula 
    • Rename the column if needed
    Description = 
SWITCH(
    'ZZK1LTSD0074K'[zz_partner_type],
    "RE", "BILL TO",
    "RG", "PAYER", 
    "WE", "SHIP_TO",
    "EL", "END USER LOC",
    "EU", "END_USER",
    "ZK", "DIST",
    "ZR", "RESELLER",
    "Unknown"  -- Default value for any unmatched partner types
)

     

    But if your dataset is Large You should use Power query:

    • Go to Power Query Editor
    • Select your table
    • Add Column → Conditional Column
    • Set up the conditions:
    Column Name: Description
If [zz_partner_type] = "RE" then "BILL TO"
Else If [zz_partner_type] = "RG" then "PAYER" 
Else If [zz_partner_type] = "WE" then "SHIP_TO"
Else If [zz_partner_type] = "EL" then "END USER LOC"
Else If [zz_partner_type] = "EU" then "END_USER"
Else If [zz_partner_type] = "ZK" then "DIST"
Else If [zz_partner_type] = "ZR" then "RESELLER"
Else "Unknown"
    • Don't forget to Refresh data after using Power query 😅.

     

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • raja1992's avatar
    raja1992
    10 months ago

    I’ve done something similar 🙂. You’ve got two good options:

    Option 1: DAX calculated column with SWITCH
    Go to Modeling → New column and use:

    Description =
SWITCH(
    [zz_partner_type],
    "RE", "BILL TO",
    "RG", "PAYER",
    "WE", "SHIP_TO",
    "EL", "END USER LOC",
    "EU", "END_USER",
    "ZK", "DIST",
    "ZR", "RESELLER",
    BLANK()    // default
)

     

    This will create the new Description column right next to your Partner type.

     

    Option 2 (cleaner for long-term):
    Make a small 2-column table (Partner type ↔ Description) and relate it to your main table. That way, if you ever add more partner types, you only update the mapping table instead of editing the DAX formula.

     

    👉 Tip: If you just need a quick fix, go with SWITCH. If you want scalability, build the small mapping table.

  • raja1992's avatar
    raja1992
    10 months ago

    I’ve done something similar 🙂. You’ve got two good options:

    Option 1: DAX calculated column with SWITCH
    Go to Modeling → New column and use:

    Description =
SWITCH(
    [zz_partner_type],
    "RE", "BILL TO",
    "RG", "PAYER",
    "WE", "SHIP_TO",
    "EL", "END USER LOC",
    "EU", "END_USER",
    "ZK", "DIST",
    "ZR", "RESELLER",
    BLANK()    // default
)

     

    This will create the new Description column right next to your Partner type.

     

    Option 2 (cleaner for long-term):
    Make a small 2-column table (Partner type ↔ Description) and relate it to your main table. That way, if you ever add more partner types, you only update the mapping table instead of editing the DAX formula.

     

    👉 Tip: If you just need a quick fix, go with SWITCH. If you want scalability, build the small mapping table.

5 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    10 months ago

    Hi Harish85,

     

    The easiest way is to use DAX:

    • Go to Data view
    • Select your table ZZK1LTSD0074K
    • Click New Column
    • Paste the formula 
    • Rename the column if needed
    Description = 
SWITCH(
    'ZZK1LTSD0074K'[zz_partner_type],
    "RE", "BILL TO",
    "RG", "PAYER", 
    "WE", "SHIP_TO",
    "EL", "END USER LOC",
    "EU", "END_USER",
    "ZK", "DIST",
    "ZR", "RESELLER",
    "Unknown"  -- Default value for any unmatched partner types
)

     

    But if your dataset is Large You should use Power query:

    • Go to Power Query Editor
    • Select your table
    • Add Column → Conditional Column
    • Set up the conditions:
    Column Name: Description
If [zz_partner_type] = "RE" then "BILL TO"
Else If [zz_partner_type] = "RG" then "PAYER" 
Else If [zz_partner_type] = "WE" then "SHIP_TO"
Else If [zz_partner_type] = "EL" then "END USER LOC"
Else If [zz_partner_type] = "EU" then "END_USER"
Else If [zz_partner_type] = "ZK" then "DIST"
Else If [zz_partner_type] = "ZR" then "RESELLER"
Else "Unknown"
    • Don't forget to Refresh data after using Power query 😅.

     

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    10 months ago

    Hi Harish85 ,
    I would also take a moment to thank Ahmed-Elfeel     , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

     

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      10 months ago

      Hi Harish85 ,

      I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

       

  • raja1992's avatar
    raja1992
    Resolver I
    10 months ago

    I’ve done something similar 🙂. You’ve got two good options:

    Option 1: DAX calculated column with SWITCH
    Go to Modeling → New column and use:

    Description =
SWITCH(
    [zz_partner_type],
    "RE", "BILL TO",
    "RG", "PAYER",
    "WE", "SHIP_TO",
    "EL", "END USER LOC",
    "EU", "END_USER",
    "ZK", "DIST",
    "ZR", "RESELLER",
    BLANK()    // default
)

     

    This will create the new Description column right next to your Partner type.

     

    Option 2 (cleaner for long-term):
    Make a small 2-column table (Partner type ↔ Description) and relate it to your main table. That way, if you ever add more partner types, you only update the mapping table instead of editing the DAX formula.

     

    👉 Tip: If you just need a quick fix, go with SWITCH. If you want scalability, build the small mapping table.

  • raja1992's avatar
    raja1992
    Resolver I
    10 months ago

    I’ve done something similar 🙂. You’ve got two good options:

    Option 1: DAX calculated column with SWITCH
    Go to Modeling → New column and use:

    Description =
SWITCH(
    [zz_partner_type],
    "RE", "BILL TO",
    "RG", "PAYER",
    "WE", "SHIP_TO",
    "EL", "END USER LOC",
    "EU", "END_USER",
    "ZK", "DIST",
    "ZR", "RESELLER",
    BLANK()    // default
)

     

    This will create the new Description column right next to your Partner type.

     

    Option 2 (cleaner for long-term):
    Make a small 2-column table (Partner type ↔ Description) and relate it to your main table. That way, if you ever add more partner types, you only update the mapping table instead of editing the DAX formula.

     

    👉 Tip: If you just need a quick fix, go with SWITCH. If you want scalability, build the small mapping table.