How to create new column in power bi using given string match condition in first column and get value from another column, make new column?

Question

My table is as follow

Col1 Col2

11_A    9
12_B    8
13_C    7
14_A    6
15_A    4

The table we need after the query

Col1  Col2  Col3

11_A    0   9
12_B    8   0
13_C    7   0
14_A    0   6
15_A    0   4

My query is

Col3 = 
LEFT( 'Table'[Col2], 
     SEARCH("A", 'Table'[Col1], 0, 
         LEN('Table'[Col1])
     )
)

score 2 · Accepted Answer · edited Oct 30 '19 at 06:21

2

Go to the query designer Add Column > Custom Column and use the following expression:

Update

You need two expressions (two new columns) for this:

One is:

'Your Column3
=if Text.Contains([Col1], "A") = true then [Col2] else 0

And the second:

'Your Column2
=if Text.Contains([Col1], "A") = false then [Col2] else 0

edited Oct 30 '19 at 06:21

KARTHIKEYAN.A

answered Oct 30 '19 at 05:30

Strawberryshrub

For me it throwing an error something like given below ```The syntax for 'Text' is incorrect. (DAX(if Text.Contains([Col1],"A") = true then Text.Range([Col1],1,1) else 0))```, i'm using windows application. – KARTHIKEYAN.A Oct 30 '19 at 05:52
This is M not DAX. You have to go to `Edit Queries > Add Column > Custom Column` – Strawberryshrub Oct 30 '19 at 05:58
I'm getting Col3 is 1,0,3,0,5 that means Col1 values, not Col2 value, what we expected. Can you recheck the query, please. – KARTHIKEYAN.A Oct 30 '19 at 06:05
I'm changed value due to understanding, sorry if you feel bad. – KARTHIKEYAN.A Oct 30 '19 at 06:08
1

`If Text.Contain([Col1], "A") = ture then [Col2] else 0` Like this? – Strawberryshrub Oct 30 '19 at 06:10

Andres Martinez · Answer 2 · 2019-10-30T15:54:33.140

There are many ways to solve this, Another easy way I like to do this with no-coding is to use Conditional Columns:

see picture

2 Answers2