cancel
Showing results for
Did you mean:
Frequent Visitor

## Need Help : Convert Excel formula to Power BI

Hi,

I need the below excel formula in POWER BI QUERY

=LOOKUP(0,-SEARCH(LEFT(F2,LEN(\$B\$2:\$B\$13))+0,\$B\$2:\$B\$13),\$B\$2:\$B\$13)

Can anyone help ?

3 REPLIES 3
Microsoft

It seems there're two ways. First, you need to use M Language in Advanced Editor. You may refer to this post. Second, you may create a calculate column with Search Function. Show a sample as below:

```Column =
CALCULATE (
MAX ( Country[Country] ),
FILTER ( Country, SEARCH ( Country[Phone], Number[Number], 1, 0 ) )
)```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Hi Cherie,

For Example the Number was "914842531599" and the result i got was "599" instead of 91.

My Result should be always from the First digit and it should NOT be matching from middle of Number.

Note : my  Phone list had all the number starting with 91XXX and 91. There was NO Phone number list with 914XX.

Thanks

Microsoft

You may have a look at Search Function. The Parameters 'start_num' of this function will be the mumber which you want to start researching.

```Column =
CALCULATE (
MAX ( Table1[Name] ),
FILTER ( Table1, SEARCH ( Table1[String], Table2[Number], 1, 0 ) )
)```

If it is not your case, please share some data sample and expected output.

Regards,

Cherie

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

Announcements