cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grprem81us
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
v-cherch-msft
Microsoft
Microsoft

Hi @grprem81us

 

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:

1.png

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.

Hi Cherie,

 

Thanks for your Reply. I tried using the Formula mentioned in your reply. In some cases i am getting wrong output.

 

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 

Hi @grprem81us

 

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

 1.png

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.