cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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.

5 REPLIES 5
Super User

@mbs2016 you can use search function

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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.

Microsoft Employee

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

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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

Super User

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors