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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors