Extracting Key Word from column with checking with master search term given sorted manner

[Anonymous]

Extracting Key Word from column with checking with master search term given sorted manner

Having one master table 

TABLE 1:

SEARCH TERM -- PRIORITY

COATED PAPER-HANKUK - 1

COATED PAPER-C2S - 2

COATED PAPER/BOARD - 3

COATED PAPER UI - 4

COATED PAPER TITAN - 5

Coated Paper - 6

Need to search above keyword from below table & extract given key word on next column. Searching to be done as per sorting order given. 

Table 2: 

SEARCH VALUES ----EXTRACT KEY WORD

ONE SIDE COATED PAPER GLOSS GSM 80 (SIZE:52CM)

ONE SIDE COATED PAPER GLOSS GSM 80 (SIZE:153CM)

COATED PAPER IN ROLLS

COATED PAPER TITAN ART ECO 200 GSM 510 X 725 MM

COATED PAPER TITAN ART ECO 118 GSM 584 X 910 MM

Please advise how to process this using DAX formula or power query.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Here is one way with DAX. A little hardcoded.

Column = 
var _p1 = MAXX(FILTER('Table 1','Table 1'[PRIORITY]=1),'Table 1'[SEARCH TERM])
var _p2 = MAXX(FILTER('Table 1','Table 1'[PRIORITY]=2),'Table 1'[SEARCH TERM])
var _p3 = MAXX(FILTER('Table 1','Table 1'[PRIORITY]=3),'Table 1'[SEARCH TERM])
var _p4 = MAXX(FILTER('Table 1','Table 1'[PRIORITY]=4),'Table 1'[SEARCH TERM])
var _p5 = MAXX(FILTER('Table 1','Table 1'[PRIORITY]=5),'Table 1'[SEARCH TERM])
var _p6 = MAXX(FILTER('Table 1','Table 1'[PRIORITY]=6),'Table 1'[SEARCH TERM])
return
SWITCH(TRUE(),
SEARCH(_p1,'Table 2'[SEARCH VALUES],1,-1)>0,_p1,
SEARCH(_p2,'Table 2'[SEARCH VALUES],1,-1)>0,_p2,
SEARCH(_p3,'Table 2'[SEARCH VALUES],1,-1)>0,_p3,
SEARCH(_p4,'Table 2'[SEARCH VALUES],1,-1)>0,_p4,
SEARCH(_p5,'Table 2'[SEARCH VALUES],1,-1)>0,_p5,
SEARCH(_p6,'Table 2'[SEARCH VALUES],1,-1)>0,_p6,
BLANK())

If your keyword table 1 is not fixed or its number of rows is changed dynamically, using Power Query to do that may be better. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Great v-Jingzhang! Given some solution. But here I am having huge database. 

Againt Priority 1 - we had more than 700 terms, priority 2 - 70 terms; like soon 30 priorities each min 10-30 terms are there. How to perform the same at higher level database.

Please share your email - hence can share sample file to you. 

[v-jingzhang]

Hi @Anonymous 

It seems the problem is more complicated than the original one. You mean that every priority has multiple terms, so we need to find out all terms for the highest priority each row has?

Jing

[Anonymous]

Dear Team,

This is complex working - where key word to be extracted as per sorting - small to large.

Something like Loop it should run & fetch data.

Master data with key words & priority key word to be picked from 1 to largest no.

SEARCH TERM	Priority
COATED BOARD	9
COATED BOARD (CHROME BOARD)	7
COATED BOARD (WHITE BOARD)	8
COATED BOARD ART PAPER	6
COATED BOARD CHIEFTAIN	5
COATED BOARD GLOSS	4
COATED BOARD GLOSS C2S	3
COATED BOARD HIKOTE	2
COATED BOARD NINGBO STAR	1

Please find raw data & extraction example.

Raw Data	Key Word
COATED BOARD NINGBO STAR dsdfgr 884772 0443	COATED BOARD NINGBO STAR
COATED BOARD NINGBO STAR dsdfgr 88dsgrwty	COATED BOARD NINGBO STAR
74762 COATED BOARD NINGBO STAR038 055	COATED BOARD NINGBO STAR
COATED BOARD NINGBO STAR 9539 dsrf 543	COATED BOARD NINGBO STAR
COATED BOARD GLOSS C2S r3ty35y	COATED BOARD GLOSS C2S
COATED BOARD GLOSS C2Sdf hgfhj	COATED BOARD GLOSS C2S
464y COATED BOARD GLOSS C2S	COATED BOARD GLOSS C2S
COATED BOARD GLOSS C2S fgjjydg	COATED BOARD GLOSS C2S
COATED BOARD GLOSS fdhtuj	COATED BOARD GLOSS
fdnhdfgh COATED BOARD GLOSS hkjdghjfg	COATED BOARD GLOSS
COATED BOARD GLOSS46647 b64456	COATED BOARD GLOSS
e53y COATED BOARD     (CHROME BOARD) ..fdfgnk35	COATED BOARD (CHROME BOARD)
COATED BOARD (CHROME BOARD)  sdfgfsgjfgdj	COATED BOARD (CHROME BOARD)
535 COATED BOARD (CHROME BOARD) dfsh d	COATED BOARD (CHROME BOARD)
COATED BOARD (CHROME BOARD) fgjfsvc	COATED BOARD (CHROME BOARD)
COATED BOARD (CHROME BOARD)  dfhsj	COATED BOARD (CHROME BOARD)
COATED BOARD fdhsdhfg	COATED BOARD
dsgdfg COATED BOARD 564 fdsfh	COATED BOARD
dsgdfg COATED BOARD fdhsdhfg	COATED BOARD

Please give solution how to resolve this issue. 

[Anonymous]

No. Need solution for my question yet. 

Extracting Key Word from column with checking with master search term given sorted manner

Having one master table 

TABLE 1:

SEARCH TERM -- PRIORITY

COATED PAPER-HANKUK - 1

COATED PAPER-C2S - 2

COATED PAPER/BOARD - 3

COATED PAPER UI - 4

COATED PAPER TITAN - 5

Coated Paper - 6

Need to search above keyword from below table & extract given key word on next column. Searching to be done as per sorting order given. 

Table 2: 

SEARCH VALUES ----EXTRACT KEY WORD

ONE SIDE COATED PAPER GLOSS GSM 80 (SIZE:52CM) - output - Coated Paper

ONE SIDE COATED PAPER GLOSS GSM 80 (SIZE:153CM)- output - Coated Paper

COATED PAPER IN ROLLS - output - Coated Paper

COATED PAPER TITAN ART ECO 200 GSM 510 X 725 MM - output - COATED PAPER TITAN

COATED PAPER TITAN ART ECO 118 GSM 584 X 910 MM- output - COATED PAPER TITAN

Please advise how to process this using DAX formula or power query.

[Anonymous]

TABLE 1:

SEARCH TERM -- PRIORITY

COATED PAPER-HANKUK - 1

COATED PAPER-C2S - 2

COATED PAPER/BOARD - 3

COATED PAPER UI - 4

COATED PAPER TITAN - 5

Coated Paper - 6

Need to search above keyword from below table & extract given key word on next column. Searching to be done as per sorting order given. 

Table 2: 

SEARCH VALUES ----EXTRACT KEY WORD

ONE SIDE COATED PAPER GLOSS GSM 80 (SIZE:52CM) - output - Coated Paper

ONE SIDE COATED PAPER GLOSS GSM 80 (SIZE:153CM)- output - Coated Paper

COATED PAPER IN ROLLS - output - Coated Paper

COATED PAPER TITAN ART ECO 200 GSM 510 X 725 MM - output - COATED PAPER TITAN

COATED PAPER TITAN ART ECO 118 GSM 584 X 910 MM- output - COATED PAPER TITAN

[smpa01]

@Anonymous  Searching to be done as per sorting order given - what does it mean?

[smpa01]

@Anonymous  what is the desired output for the given sample?