new table from existing table with rows contains specific word/text in specific column

[roniedhaka]

hi,

i need help to extract specific word/text from a string of characters - like text/word example 'wifi' or 'wi-fi' or 'AP' etc and generate a new table with only rows with that contain specific text/word from a specific column cell.

lets say i have a existing power BI table with 100+ rows and 8 columns. in some rows has this type of text/word in column number-4.

I need to generate a new table with rows contains that informations (rows contain that text/word) & discard rest of rows. How?

[Arul · Accepted solution]

@roniedhaka , try this

New Table = FILTER('Table',
CONTAINSSTRING('Table'[fruits],"Mango") ||
CONTAINSSTRING('Table'[fruits],"lichie") ||
CONTAINSSTRING('Table'[fruits],"lichee"))

[Arul]

@roniedhaka ,

Can you provide atleat 5-10 rows of sample data?

[roniedhaka]

country-id	account-id	fruits	weight /kg	price total kusd	year
1234	abcd123	mango	4321	15	2021
5678	efgh456	lichee	1234	18	2022
3456	ijkl789	dates_mango_orange	2468	22	2024
6789	mnop246	Mango-steen	1357	21	2021

here is an example table.

i need to transform this table into a new table but limited rows which fruits column contains the word 'mango' or 'Mango' or 'Mango-steen' (appearently 3 rows as per above table, and rest rows discarded).

[Arul]

@roniedhaka ,

try this

New Table = FILTER('Table',CONTAINSSTRING('Table'[fruits],"Mango"))

[roniedhaka]

thanks,  sorry i ahve to add few more rows to explain a little more.

what i can do if also keep mango and lichee and lichi?

country-id	account-id	fruits	weight /kg	price total kusd	year
1234	abcd123	mango	4321	15	2021
5678	efgh456	lichee	1234	18	2022
3456	ijkl789	dates_mango_orange	2468	22	2024
6789	mnop246	Mango-steen	1357	21	2021
2468	wxyz024	mangosteen & apple	1234	20	2020
1357	qrst135	jackfruit	5678	19	2024
5432	uvwx345	lichie	3456	22	2023

[Arul · Accepted solution]

@roniedhaka , try this

New Table = FILTER('Table',
CONTAINSSTRING('Table'[fruits],"Mango") ||
CONTAINSSTRING('Table'[fruits],"lichie") ||
CONTAINSSTRING('Table'[fruits],"lichee"))

[roniedhaka]

many thanks, this should be it.

one more thing - if i want year 2021 & 2023 rows only, with those specific word/text?

[Arul · Accepted solution]

@roniedhaka ,

this should work,

New Table = FILTER(
    'Table',
    (CONTAINSSTRING('Table'[fruits], "Mango") ||
     CONTAINSSTRING('Table'[fruits], "lichie") ||
     CONTAINSSTRING('Table'[fruits], "lichee")) &&
    ('Table'[year] = 2023 || 'Table'[year] = 2021)
)

[roniedhaka]

hi, thanks. that solved my problem 80%. But still i have some issues. Let me give the details of it.

the word/text i choose like mango, it is easy to extract. But in real problem i have to extraxt a specific word/text which sometimes appears inside within a big text/word and that unwanted row is chosen.

example - i like to extract word 'AX' & 'wifi' & 'wi-fi' only. Sometimes AX is in mix with other letters formed new word like 'Max', 'Tax', 'dax' etc. Means ax is inside of these words and those rows are unwanted.

i want - 

1. only rows with containts string 'AX' only. Not mix with other letter. Those max, tax, dax rows we can avoid.
2. If max, tax, dax type rows contain 'wifi' or 'wi-fi' - i need that row.
3. 'wifi' text also need with same attributes like point-1, only word with 'wifi' or 'wi-fi'