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?

0
votes

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])
     )
)
2
powerbipowerbi-desktoppowerbi-embeddedpowerbi-datasourcepowerbi-custom-visuals

2 Answers

2
votes

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
2
votes

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