Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MatiasSousa
Regular Visitor

Fetch text in cell and return corresponding value

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.
1 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:

Afbeelding1.png 

Hope this will help you!

View solution in original post

6 REPLIES 6
danielkrol
Helper II
Helper II

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?

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.

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:

Afbeelding1.png 

Hope this will help you!

@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:

MatiasSousa_1-1595279329757.png

 

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.

@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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.