Forum Discussion
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.
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.
8 Replies
Anonymous what is the desired output for the given sample?
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
Anonymous Searching to be done as per sorting order given - what does it mean?
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.
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.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 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
