CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-embeddedpowerbi-datasourcepowerbi-custom-visuals
KARTHIKEYAN.A
KARTHIKEYAN.A

Reputation: 20148

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?

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])
     )
)

Upvotes: 0

Views: 11553

Answers (2)

Andres Martinez
Andres Martinez

Reputation: 244

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

  • In PBI select Power Query Editor
  • Select your table on the edge of the screen
  • Select Add Column tab
  • Select Conditional Columns...
  • Name your column
  • Enter your condition as in the picture
  • You can add several conditions if you like
  • Don't forget to format your column to numeric if needed.

see picture

Adding columns using Conditional Column

Upvotes: 2

Strawberryshrub
Strawberryshrub

Reputation: 3399

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

enter image description here

enter image description here

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

Upvotes: 2

Related Questions