Forum Discussion
Solved
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.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!
6 Replies
- danielkrol
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.
- danielkrol
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!
