Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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.
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.
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?
@Anonymous what is the desired output for the given sample?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.