Fetch text in cell and return corresponding value

[MatiasSousa]

Hello,

I need help.

I currently use this formula in excel:
= LOOKUP(3 ^ 15; FIND (Validation! $ E $ 11: $ E $ 35; [@ Standards]; 1); Validation! $ D $ 11: $ D $ 35)
However, I need to recreate it in power bi due to automation with a company system.

We have a column with many lines and in this column there are INMETRO rules and ordinances, the ordinances are always present, however the rules always vary from one process to another, which makes it difficult to lookupvalue, which always seeks the exact match.
In theory the formula above is looking in column X for the text of COLUMN B, when locating the respective sequence it returns the values ​​of COLUMN A.
As below:

COL A   COL B
RES.   SEARCH
144    INMETRO Ordinance No. 144
170    INMETRO Ordinance No. 170
344    INMETRO Ordinance No. 344
371    INMETRO Ordinance No. 371
400    INMETRO Ordinance No. 400
430    INMETRO Ordinance No. 430
446    INMETRO Ordinance No. 446
497    INMETRO Ordinance No. 497
553    INMETRO Ordinance No. 553

Unfortunately for containing customer information I cannot provide so many details.

[danielkrol]

I'm not totally sure what you are looking for. Do you want the number that is at the end of column B and place that in column A?

[MatiasSousa]

I am looking for the entire string (INMETRO Ordinance No. 371) in the text, for example: "IEC 60335-1: 2010; INMETRO Ordinance No. 371, of December 29, 2009; IEC 60335-2-23: 2003 + A1: 2008 + A2: 2012" When you find this string, return only the final number, in this case 371.

[danielkrol · Accepted solution]

Oke. I think you might want to try to add a column to your table in Query Editor. You can type it in the Advanced Editor, or you can use the "Add column > extract > text between delimiters".

In the advanced editor it will look like this:

    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "INMETRO Ordinance No.", ","), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", Int64.Type}})

In UI something like this:

 

Hope this will help you!

[MatiasSousa]

@danielkrol Wow, thank you. I didn't remember that function. Sorry, I'm quite new to Power BI.

I have only two problem, sometimes the employee did not insert the comma after the Ordinance number:

We also have the problem that, there are cases that for a single process there are complementary ordinances that sometimes come before the main ordinance that I am looking for, for example:

But I can use the replacement tool for that, I believe.

[danielkrol]

@MatiasSousa you can try adding a space after your "no" sign, so your first delimiter is "no ", and then your second delimiter is also a space (" "). You then need to insert a step to get rid of the comma in some cases. You can use the replace function for that.

Hope you can get it to work!

[MatiasSousa]

For example:

https://drive.google.com/file/d/1txM7YhTdT74NivqlBidDixtVWh6Rn7tg/view?usp=sharing