cancel
Showing results for
Did you mean:
New Member

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

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.

1 ACCEPTED SOLUTION
Community Support

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.

8 REPLIES 8
Community Support

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.

New Member

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.

Community Support

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

New Member

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.

New Member

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.

New Member

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

Super User

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

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

@113399_itc  what is the desired output for the given sample?

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.