Need direction on how to convert an excel formula so it can be used in Power BI (scoring formula)

[mbs2016]

Below is an example of the formula|

=IFERROR(IF(SEARCH(K$3,$B4,1)>0,K$2,0),0)

Ultimatley trying to score data.  Example:  If Column 'A' has value of "Address1" then subtract 10 (out of 100), if  Column A has value of "ShipToAddress1" then subtract 20, etc.

[parry2k]

@mbs2016 you can use search function

New Column  =
IF( SEARCH( Table[Col1], Table[Col2],, 0 ) > 0, Table[Col3], 0)

[mbs2016]

Thank you for the prompt response.  Example of the spreadsheet I'm trying to convert for as more detailed example.

B	C	D	E	F	G	H	I
	B	100		-25	-15	-10	-10
PI Team Comments	-----	Score	Spacing	AP_INV	AP_PO	AP_SHIP	AP_TAX
AP_ALL CORRECT		100		0	0	0	0
AP_SHIPPING_M		90		0	0	-10	0
AP_SHIPTO_I		90		0	0	-10	0
AP_SHIPTO_I		90		0	0	-10	0
AP_TAX_M		90		0	0	0	-10
AP_TAX_I  |  AP_SHIPTO_M		80		0	0	-10	-10
AP_TAX_I  |  AP_SHIPTO_M		80		0	0	-10	-10
AP_TAX_M  |  AP_SHIPTO_M		80		0	0	-10	-10
AP_INV NUM_I |		75		-25	0	0	0

Formul used to score the LAST row  = =IFERROR(IF(SEARCH(F$4,$B13,1)>0,F$3,0),0)

My concern is that the data was sourced from a SQL DB table and exported to Excel where the formulas were added.  The column that needs to be reported on is a free form text field that can have one of twelve possible values listed and a score is assigned to each.

For example:  Out off a total possible score of 100...

Possible values include "AP_INV" (If this value is present the subtract -25), "AP_SHIP" ( (If this value is present the subtract -10), etc.  

[v-danhe-msft]

Hi @mbs2016,

Could you mean below is your sample data table:

B	C	D	E	F	G	H	I
	B	100		-25	-15	-10	-10
PI Team Comments	-----	Score	Spacing	AP_INV	AP_PO	AP_SHIP	AP_TAX
AP_ALL CORRECT		100		0	0	0	0
AP_SHIPPING_M		90		0	0	-10	0
AP_SHIPTO_I		90		0	0	-10	0
AP_SHIPTO_I		90		0	0	-10	0
AP_TAX_M		90		0	0	0	-10
AP_TAX_I  |  AP_SHIPTO_M		80		0	0	-10	-10
AP_TAX_I  |  AP_SHIPTO_M		80		0	0	-10	-10
AP_TAX_M  |  AP_SHIPTO_M		80		0	0	-10	-10
AP_INV NUM_I |		75		-25	0	0	0

Could you please post your desired result if possible?

Regards,

Daniel He

[mbs2016]

Is it possible to attach a copy of the Excel spreadsheet in this forum?

[parry2k]

@mbs2016 youhave to post it on onedrive/google drive and share link.