cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
113399_itc
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
v-jingzhang
Community Support
Community Support

Hi @113399_itc 

 

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

21120304.jpg

 

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.

 

View solution in original post

8 REPLIES 8
v-jingzhang
Community Support
Community Support

Hi @113399_itc 

 

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

21120304.jpg

 

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.

 

View solution in original post

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. 

 

Hi @113399_itc 

 

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

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 TERMPriority
COATED BOARD9
COATED BOARD (CHROME BOARD) 7
COATED BOARD (WHITE BOARD)8
COATED BOARD ART PAPER6
COATED BOARD CHIEFTAIN5
COATED BOARD GLOSS4
COATED BOARD GLOSS C2S3
COATED BOARD HIKOTE2
COATED BOARD NINGBO STAR1

 

Please find raw data & extraction example.

 

Raw DataKey Word
COATED BOARD NINGBO STAR dsdfgr 884772 0443COATED BOARD NINGBO STAR
COATED BOARD NINGBO STAR dsdfgr 88dsgrwtyCOATED BOARD NINGBO STAR
74762 COATED BOARD NINGBO STAR038 055COATED BOARD NINGBO STAR
COATED BOARD NINGBO STAR 9539 dsrf 543COATED BOARD NINGBO STAR
COATED BOARD GLOSS C2S r3ty35yCOATED BOARD GLOSS C2S
COATED BOARD GLOSS C2Sdf hgfhjCOATED BOARD GLOSS C2S
464y COATED BOARD GLOSS C2SCOATED BOARD GLOSS C2S
COATED BOARD GLOSS C2S fgjjydgCOATED BOARD GLOSS C2S
COATED BOARD GLOSS fdhtujCOATED BOARD GLOSS
fdnhdfgh COATED BOARD GLOSS hkjdghjfgCOATED BOARD GLOSS
COATED BOARD GLOSS46647 b64456COATED BOARD GLOSS
e53y COATED BOARD     (CHROME BOARD) ..fdfgnk35COATED BOARD (CHROME BOARD) 
COATED BOARD (CHROME BOARD)  sdfgfsgjfgdjCOATED BOARD (CHROME BOARD) 
535 COATED BOARD (CHROME BOARD) dfsh dCOATED BOARD (CHROME BOARD) 
COATED BOARD (CHROME BOARD) fgjfsvcCOATED BOARD (CHROME BOARD) 
COATED BOARD (CHROME BOARD)  dfhsjCOATED BOARD (CHROME BOARD) 
COATED BOARD fdhsdhfgCOATED BOARD
dsgdfg COATED BOARD 564 fdsfhCOATED BOARD
dsgdfg COATED BOARD fdhsdhfgCOATED BOARD

 

Please give solution how to resolve this issue. 

113399_itc
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.

 

 

113399_itc
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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


smpa01
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors