Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Creating a Custom column based on Value from one column

Hi Guys,

 

I am just starting off in Power BI, i have following query.

I want to create custom column based on the values in another coumn of the same table.

For example, if Orignal Column contains value ABC cont or ABC Holding  then it should come to custom column as ABC ans so on for other values in original column who meets the criteria.

 

Original ColumnCustom Group
ele HealthcareEST
HIGHNESSPAH
PAHPAH
ABC ContABC
ABC Holding ABC
EST HoldingEST

 

Regards

Need Help
  • NHarington's avatar
    NHarington
    1 year ago

    Hello, 

     

    By creating a custom column in powerBI and pasting the following dax query, you will return what you are looking for. (Note that you will need to add more rows for each of your conditions. 

     

    NewColumn =
SWITCH(
    TRUE(),
    SEARCH("ABC", [OriginalColumn], 1, 0) > 0, "ABC",
    SEARCH("EST", [OriginalColumn], 1, 0) > 0, "EST",
    BLANK()  -- Or you can add another condition here if needed by simply repeating the above lines
)

     

     Please mark my answer as the solution if this works for you, feel free to respond if you need more clarification!

     

    Have a great day

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi thanks for reply

       

      Acyually the first coloumn (original coloumn) is company name and second coulmn (custom column) is group name. 

      i want build a custom coulumn based on which compnay falls under which group.

       

      Thanks

  • NHarington's avatar
    NHarington
    Frequent Visitor
    1 year ago

    Hello, 

     

    By creating a custom column in powerBI and pasting the following dax query, you will return what you are looking for. (Note that you will need to add more rows for each of your conditions. 

     

    NewColumn =
SWITCH(
    TRUE(),
    SEARCH("ABC", [OriginalColumn], 1, 0) > 0, "ABC",
    SEARCH("EST", [OriginalColumn], 1, 0) > 0, "EST",
    BLANK()  -- Or you can add another condition here if needed by simply repeating the above lines
)

     

     Please mark my answer as the solution if this works for you, feel free to respond if you need more clarification!

     

    Have a great day

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    Hi Anonymous 

     

    I think for first two rows, it's not possible in Power BI. If we exclude first two rows, then we can do it by using Power Query Editor.

     

    There is option called Column from examples.  If you use that, you'll get the expected out put. 
    Step-1

    Select the  Original Column->Addcolumn in top ribbon bar->Click on column from examples(highlighted in yellow)

    Step-2
    After clicking on column from examples you able to see below picture

    If you type first 3 letter, then hit enter button, you'll get automaticall all values as expected. Then click ok on above the first characters column.

     

    Step-3

    The just you'll rename the  first characters column as Custom Group.

     

    Thanks!