Replace yes found in one list with the actual string from another list

0 votes

I’m comparing two lists, ListA and ListB, to check if ListB’s row content appears in ListA’s row content at all. I can’t do a one-for-one match of both rows’ contents because the content of a row in ListB might only be one part of the content of a row in ListA.

PS.: In the query below, ListB includes “roo”, which is the first three letters in the word room. I would want to know that “roo” is in ListA’s row that has “in my room.”

What I want is to replace “yes” with the actual value from ListB — the value “roo,” for instance. I tried to simply substitute wordB for “yes” but wordB wasn’t recognized and an error was thrown at me.

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>if List.MatchesAny(ListB, (wordB)=>Text.Contains(lineA, wordB)) = true then "yes" else "no")
in
    contains_word

The current query results in the following :

    List
1   yes
2   yes
3   no
4   yes

I want the query results to be as the following :

    List
1   roo
2   me
3   
4   only

Any idea of how to do this?

Feb 19, 2019 in Power BI by Shubham
• 13,490 points
740 views

1 answer to this question.

0 votes

Objective : The idea is to use List.Transform twice, so that; 

  • Inner Transform : this list B is changed to leave only matching values
  • Outer Transform : the string from list A is replaced by the first non-null entity.

Code :

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>List.Select(List.Transform(ListB, (wordB)=>if Text.Contains(lineA, wordB) = true then wordB else null), (x)=>x <> null){0}?)
in
    contains_word

answered Feb 19, 2019 by Upasana
• 8,620 points

Related Questions In Power BI

0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14, 2019 in Power BI by Cherukuri
• 33,030 points
2,570 views
0 votes
0 answers

After connecting SharePoint List with Power BI, and editing data in query, all the data I want are in links

I've try so many ways to decode ...READ MORE

Jun 18, 2020 in Power BI by Dora
• 120 points
1,115 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,540 points
1,549 views
0 votes
1 answer

Power Query: Adding a Special Case

Try adding a Group By with Minimum ...READ MORE

answered Dec 27, 2018 in Power BI by Upasana
• 8,620 points
616 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,652 views
0 votes
1 answer

Cumulative row data over last 12 months in Power Query

Basically, what you do is,  Add an Index,  Group ...READ MORE

answered Apr 1, 2019 in Power BI by Upasana
• 8,620 points
3,087 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
6,623 views
0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 8,620 points
1,371 views
0 votes
1 answer

Bar chart : Arranging in descending order using data from another chart

Select treemap.  Make it active.  Turn on Visual Interactions ...READ MORE

answered Feb 8, 2019 in Power BI by Upasana
• 8,620 points
756 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP