Lookup text from one table

[sohaibnomani]

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

[camargos88 · Accepted solution]

Hi @sohaibnomani ,

Check this file: Download PBIX 

[Ashish_Mathur · Accepted solution]

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)))

[sohaibnomani]

Ashish 

The solution also worked. But I would love if you can explaing how this worked.

[sohaibnomani]

Thanks Ashsish for ur support. I will also try with your provided solution.

[camargos88 · Accepted solution]

Hi @sohaibnomani ,

Check this file: Download PBIX 

[sohaibnomani]

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

[camargos88]

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"}));

[sohaibnomani]

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 .