Forum Discussion
Lookup text from one table
I have two tables, 1. store code area: there are two columns PLANT AREA, STORE CODE 2. Revenue. There is a column named "item code" in the "revenue" table. this column contans either a number or a string alphanumeric characters. Following is what i require
add a new column in the 'revenue' table with the following condition.
1. if the record is a number, than fill it with text "CONS"
2 if its alphanumeric, then extract the alphabets only and lookup the characters from the column 'store code' of 'store code area' table and upon match, use the relevent data from the column 'plant area' from the table 'store code area'.
Basically its the lookup of text, which unfortunately quite complex in power bi which is quite strange. should have been like its in excel.
Store code area table sample
| PLANT AREA | STORE CODE |
| GT | GTT |
| GT | GTG |
| ST | STT |
| ST | STG |
Revenue table relevent column
Item no.
| 012500006 |
| 012500011 |
| 11GTT0465 |
| 11GTT0687 |
| 012500007 |
| 019500001 |
| 015200015 |
| 015200016 |
| 015100019 |
| 017100002 |
| 060100032 |
| 060100001 |
| 30CVI0040 |
| 11GTT0458 |
| 30CVI0022 |
Hi,
This calculated column formula works
=if(ISNUMBER(IFERROR(1*revenue[Item no.],BLANK())),"Cons",LOOKUPVALUE(store_code_area[PLANT AREA],store_code_area[STORE CODE],FIRSTNONBLANK(FILTER(VALUES(store_code_area[STORE CODE]),SEARCH(store_code_area[STORE CODE],revenue[Item no.],1,0)),1)))
7 Replies
camargos
it worked like a charm. Thanks a lot
Just to add for those who would serk help from this post that the "merge" feature in power bi is basically a "word lookup" unlike the formula "lookupvalues" which only looks for numbers .
Camargos
Thanks for your solution. I would ask for another favour. I am new to power BI, if you can explain to me as well how the script is working, it would be helpful for my learning.
Thanks
Hi sohaibnomani ,
I used this code to exclude the numbers from the values:
let
_alpha = {"A".."Z"},
_item = [#"Item no."] in
if List.Count(List.Select(_alpha, each Text.Contains(_item, _))) > 0 then
Text.Remove(_item, {"0".."9"})
else "CONS"1) List with alphabet values and used variables for it and the current item;
2) Check if the _item has any item from the _alpha list (List.Count(List.Select(_alpha, each Text.Contains(_item, _))));
3) If yes, just remove the numbers from it (Text.Remove(_item, {"0".."9"}));
Hi,
This calculated column formula works
=if(ISNUMBER(IFERROR(1*revenue[Item no.],BLANK())),"Cons",LOOKUPVALUE(store_code_area[PLANT AREA],store_code_area[STORE CODE],FIRSTNONBLANK(FILTER(VALUES(store_code_area[STORE CODE]),SEARCH(store_code_area[STORE CODE],revenue[Item no.],1,0)),1)))Thanks Ashsish for ur support. I will also try with your provided solution.
Ashish
The solution also worked. But I would love if you can explaing how this worked.
